Weighted Average on Narrow Table Using DAX Measure

[WishAskedSooner]

My Fact Table is quite narrow with a few Dimension tables (think EAV model if you are familiar with dB architecture). Let's say I have the following 'Fact' table:

EntryID	AttrID	Value
1	1	5
2	1	10
1	2	15
2	2	20

and the following 'Dim' table with a one-to-many join on [AttriID] in the Data Model:

AttrID	Category
1	A
2	B

For the most part, my measures rely on:

CALCULATE(SUM('Fact'[Value]), ALL('Dim'), 'Dim'[Category] = "A")

which correctly returns 15. This works fine when I just need a simple aggregation like SUM, AVERAGE, etc. for a given Category.

For a handful of my measures, I have to wheel out the DIVIDE function to get to the proper aggregated value. This is simple if all I am doing is a SUM(A) / SUM(B) e.g.

DIVIDE(CALCULATE(SUM('Fact'[Value]), ALL('Dim'), 'Dim'[Category] = "A"), CALCULATE(SUM('Fact'[Value]), ALL('Dim'), 'Dim'[Category] = "B"))

which correctly returns 15 / 35 or 0.4285....

But, sometimes I need SUM(A(i)*B(i)) / SUM(B) where i is an index across all EntryIDs, but I can't get it to work. To be precise, I need (5*15 + 10*20) / (15 + 20) which equals 7.857...
Part of the complication arises comes from joining up the proper values by EntryID versus (5*20 + 10*15) or some other jumbled order when doing this across millions of rows.

I have researched this for hours with no solutions. And, for design reasons, I need to do this in a measure that is a single DAX line that does not rely on defining VARs, etc.

Please help and thank you in advance!

[v-jiewu-msft · Accepted solution]

Hi @WishAskedSooner ,

First of all, many thanks to @lbendlin  for your very quick and effective replies.

Based on my testing, please try the following methods:

1.Create the simple table.

2.Create the new measure to calculate sum across all entry id.

Weighted Average = 
DIVIDE(
    SUMX(
        FILTER(
            'Fact Table',
            RELATED('Dim Table'[Category]) = "A"
        ),
        'Fact Table'[Value] * LOOKUPVALUE('Fact Table'[Value], 'Fact Table'[EntryID],'Fact Table'[EntryID], 'Dim Table'[Category], "B")
    ),
    CALCULATE(SUM('Fact Table'[Value]), ALL('Dim Table'), 'Dim Table'[Category] = "B")
)

3.Select the measure and edit the number of shown for the value.

4.Drag the measure into the card visual. The result is shown below.

You can also view the following links to learn about DAX function.

LOOKUPVALUE function (DAX) - DAX | Microsoft Learn

SUMX function (DAX) - DAX | Microsoft Learn

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[WishAskedSooner]

@v-jiewu-msft 

Simply, simply Brilliant!!!! I was able to implement this into my actual data model which is slightly more complex than the example I provided (a total of three columns that need to be included in the LOOKUP to filter my fact table properly) and it works beautifully! I can't even begin to describe how thankful I am for your help. I have been agonizing over this for days. Thank you!

[WishAskedSooner]

Here is hopefully more meaningful data that fully describes my problem. I have three tables:

Fact

DimE joined on EID

DimA joined on AID

and the following Data Model:

What I am trying to do is multiply Category A with Category B (not Category C) for EID X. Repeat for EID Y. Then sum the individual results. I have created a Pivot to illustrate:

The final result would be 75 + 200 = 275. In other words, I am trying to do the following:

(5*15 + 10*20) = 275

I am hoping I can do this in a DAX measure versus having to actually pivot the Fact table to a new table.

I hope this makes more sense. Thanks in advance for the help!

[WishAskedSooner]

It was suggested I provide sample data that fully covers my issue. So I will try to do that. I have the following three tables:

Fact

DimE joined on EID

DimA joined on AID

The following Data Model

I am trying to find a way to multiply the values for Category A and Category B (not Category C) in the Fact table by EID then sum the individual results.

For example, for EID 1, I want to multiply AID 1 and AID 2 together because they both belong to EID 1. Furthermore, I want to be able to specify this using the joined Category in DimA, i.e. for EID 1 multiply Category A by Category B. Then, for EID 2, multiply Category A and Category B. Repeat for each EID in the Fact table. Then sum the individual results. So,

(5*15 + 10*20) = 275

I would provide examples of what I have tried, but it is pointless because none come even close. I literally have no idea how to do this. Please help.

[WishAskedSooner]

Thank you for your reply. You seem to imply that I could use either SUMX or PRODUCTX to get to the correct value which is interesting.

However, I am very new to PowerBI and DAX, so if you could provide the syntax with how to use those functions with or without CALCULATE, that would be a huge help. As I mentioned above, I have researched this for at least four hours without success, and this includes experimenting with SUMX and PRODUCTX.

I am not sure what you mean by providing sample data. I can't upload my actual PBI model due to security reasons. I suppose I could create a PBI project with the data that I included above, but is that extra effort really necessary?

[lbendlin]

It's totally your choice.  I can only assist with meaningful sample data.

[lbendlin]

use aggregation functions like SUMX or PRODUCTX.

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.