Weighted Average Cost with unavoidable Many-to-Many Relationship

[ibesmond]

I’m hoping this is an easy answer. I’m dealing with an issue with a report that I am building that I
can’t seem to avoid a many-to-many relationship because of granularity.

Many-to-Many Sample.xlsx (25.3 KB)
Many-to-Many Sample.pbix (50.4 KB)

I have one table that has an agreement number and a load number, and how much was in the load.

Then I have a cost table that has the corresponding agreement number in it (what I used for the relationship) a cost group and a cost amount.

I’m trying to get the total to calculate correctly.

I tried to provide a sample isolating the processing, but I deleted some of the sample data in the process, so It may not tie back to the original sample data set.

Sample solution.xlsx (56.9 KB)

[BrianJ]

@ibesmond ,

Not an easy answer, but where there’s a will, there’s often a way to get rid of the Many to Many. Here I created a table of unique agreement numbers that I used to filter both the Load and Cost tables. This should be done in Power Query, but I thought it was easier to illustrate the concept via DAX:

Unique Agreements = 
DISTINCT(
    UNION(
        DISTINCT( Cost[Agreement] ),
        DISTINCT( Load[Agreement] )
    )
)

Now to get the correct total average cost:

Average Cost w Total = 

VAR vTable = 
SUMMARIZECOLUMNS(
    'Unique Agreements'[Agreement],
    Load[Agreement],
    Load[Volume],
    Cost[Cost Group],
    Cost[Cost],
    "@AvgCost", [Average Cost]
)

VAR Result = 
IF(
    HASONEVALUE( 'Unique Agreements'[Agreement] ),
    [Average Cost],
    SUMX( vTable, [@AvgCost] )
    )

RETURN Result

Note that I had to reduce the size of your dataset to check my work by exporting the CSV from the table visual to Excel (totals checked out);

I hope this is helpful. Solution file attached.

• Brian
  eDNA Forum - Many-to-Many Sample Solution.pbix (41.1 KB)

[ibesmond]

Thank you for your response @BrianJ

I realized I have an agreement table with unique agreement numbers so that solved the many-to-many relationship problem.

[ibesmond]

I have my data set up now with one-to-many relationships.

I tried a solution, but it isn’t tying out.

I seem to get an error when I try to bring columns from both tables now.

I brought in ‘Agreement’[Agreement], ‘Load’[Load] and ‘Load’[Volume], but when I brought in ‘Cost’[Cost Group] and/or ‘Cost’[Cost] I get the above error. I think because I don’t have the direct relationship between the two fact tables now.

eDNA Forum - Many-to-Many Sample Solution.pbix (73.2 KB)

Not sure what else I can try. Is there a way I can add columns to the ‘Load’ Table to add each Cost Group as a column? Or do a crossfilter or lookupvalue to add multiple cost groups to each contract and the cost associated with the group and contract? Thanks.

[BrianJ]

@ibesmond ,

OK, I think we’re going to need to construct a full bridge table to get this all to work properly. It seems pretty clear to me that the combo of Agreement ID and Cost Group will produce a unique cost amount, correct? Is is also true that the combo of Agreement ID and Load will produce a unique volume amount?

• Brian

[ibesmond]

@BrianJ,

I believe so.

The agreements table is my lookup table, This has a relationship with the Loads table and the Cost table at the Agreement ID level. The problem is the granularity of the Load table is the Load level, and the granularity of the Cost table is the cost group, so I don’t know how to tie them.

The costs for the cost groups are the same for each agreement and load.

Excel Calculation.xlsx (18.4 KB)

I added an excel file to illustrate the calculation of the weighted average cost. The first problem is the table errors out in Power BI when I try to bring in the load and cost group because there is no relationship, and same with creating the calculations.

I hope this clarifies the issue. Thank you.

[BrianJ]

@ibesmond ,

Just wanted to let you know that I haven’t forgotten about this. Just been consumed by prep for the upcoming Analytics Summit. Hope to have a solution for you on Friday 2/18. Sorry for the delay.

– Brian

[BrianJ]

@ibesmond ,

Well, good news bad news on this one. The good news is that I solved the many-to-many problem with a bridge table and a DAX CROSSFILTER function.

The bad news is that the DAX to get even a simple sum to work in the proper context is so complex as to be unworkable. Back to the drawing board – trying a different idea with a merged single fact table.

We’ll keep you posted…

• Brian

[ibesmond]

One thought I had, which may be easier to implement is to get the weighed average cost as the contract summary level.

I used summarize to remove the granularity in the loads table, and summarized the volume for each agreement.

Summarized Agreement Volume =
SUMMARIZE( Load, Load[Agreement], “Summarized Volume”, [Total Volume] )

I used this table to create the one-to-many relationships.

I have an agreements table, so I don’t actually have to create a table, so I tried to create the measure using a virtual table within the DAX measure.

Summarized Agreement Volume =
SUMX( SUMMARIZE( Load, Load[Agreement], “Total Volume”, [Total Volume] ), [Total Volume] )

I wanted to be able to write the weighted average cost as one measure, but figured that branching would be a better route.

I took the summarized volume and created the measure for weighted average.

Weighted Avg Cost Correct =
DIVIDE( SUMX(‘Summarized Agreement Volume’, [Total Cost] * [Summarized Volume] ), [Summarized Agreement Volume], 0 )

Many-to-Many Sample.pbix (53.6 KB)

Does that look correct, or have I made a mistake somewhere?

[ankit]

Hi @ibesmond

Is your solution working or you still need help ?

Thanks
Ankit J

[EnterpriseDNA]

Hi @ibesmond, we’ve noticed that no response has been received from you since a few days ago.

We just want to check if you still need further help with this post?

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

[EnterpriseDNA]

Hi @ibesmond, due to inactivity, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, you can create a new thread.

[ibesmond]

It works.