Forum Discussion
Weighted Average Measure on Single Table with Multiple Filters
My apologies for being new to this forum. My first post of this problem was not specific enough. I have tried multiple times to reply with more information, but the forum never seems to let me. So, here is a second attempt with more information.
I have three tables:
Fact
DimE joined on EID
DimA joined on AID
and the following Data Model
I am trying to multiply Category A by Category B (not Category C) for each EID, then sum the individual results. I have created the following PIVOT to illlustrate:
So, the actual operation looks like the following:
(5*15 + 10*20) = 275
I am trying to do this in a measure using DAX versus actually pivoting my data to a new table. All of the potential solutions I have found point to joining two columns from two different tables to multiply. As you can see, my data sits in one table. Therefore, I need to find a way to filter the table by Category A and Category B then multiply the filtered values by EID versus some other jumbled order.
I hope this makes sense.. Thanks in advance for the help.
Try this measure:
Weighted Average = SUMX ( VALUES ( DimE[EID] ), PRODUCTX ( CALCULATETABLE ( 'Fact', DimA[Category] IN { "A", "B" } ), 'Fact'[VAL] ) )
1 Reply
- DataInsightsSuper User
Try this measure:
Weighted Average = SUMX ( VALUES ( DimE[EID] ), PRODUCTX ( CALCULATETABLE ( 'Fact', DimA[Category] IN { "A", "B" } ), 'Fact'[VAL] ) )