Forum Discussion
Weighted Average on Different Level of Aggregation using Netted Summarized Table
Hi,
I have the following test Data set, with 3 different levels of grouping. And I want to make a measure "Vaue WA" for Value, and the make a matrix like showing in the image after. Basically, it is the weighted average of "Value" based on "Market Value“ for the sub-categories contained in it. If it is on row of "Portfolio1", it is the WA of Index1 and Index2, and if it is on row of "Class1", it is the WA of "Portfolio1" and "Portfolio2" etc.
I had the code for measure "Value WA" at the bottom using netted SUMMARIZE. But somehow it doesn't work appropriately. I can't figure out why, but I guess it is on the last line of "RETURN SUMX(SubTable2, [Value2] * [WT2])"
Please help me with the correct approach. Thank you very much!
| GroupLevel1 | GroupLevel2 | GroupLevel3 | Market Value | Value |
| Class1 | Portfolio1 | Index1 | 20 | 3 |
| Class1 | Portfolio1 | Index2 | 30 | 2 |
| Class1 | Portfolio2 | Index3 | 50 | 4 |
| Class2 | Portfolio3 | Index4 | 50 | 6 |
| Class2 | Portfolio4 | Index5 | 50 | 4 |
| Class3 | Portfolio5 | Index6 | 200 | 5 |
Value WA =
IF( ISFILTERED('Table'[GroupLevel3]),
CALCULATE(SUM('Table'[Value])),
VAR SubTable1 =
ADDCOLUMNS(
SUMMARIZE('Table','Table'[GroupLevel3],'Table'[GroupLevel2],'Table'[GroupLevel1]),
"WT3", CALCULATE(SUM('Table'[Market Value])) /
CALCULATE(SUM('Table'[Market Value]),
ALL('Table'[GroupLevel3])),
"Value3", CALCULATE(SUM('Table'[Value])))
VAR Val_level2 = SUMX(SubTable1, [Value3] * [WT3])
RETURN
IF( ISFILTERED('Table'[GroupLevel2]),
Val_level2,
VAR SubTable2 = ADDCOLUMNS(
SUMMARIZE(SubTable1,[GroupLevel2]),
"WT2", CALCULATE(SUM('Table'[Market Value])) /
CALCULATE(SUM('Table'[Market Value]),
ALL('Table'[GroupLevel2])),
"Value2", Val_level2)
RETURN SUMX(SubTable2, [Value2] * [WT2])
)
)
Hi alwang ,
This has to do with the fact that you are reusing the subtable1 and the recalculating the values on top of it.
You subTable1 is returning the following values:
Has you can see the result is giving one line per grouplevel and then you pick up the values you need and make the calculations in this case for example for the Portfolio 1:
0.4* 3 + 0.6*2 = 1,2 + 1.2 = 2.4 so correct result has you expect.
If I know pick your second table and see the values you get:
What is happenning here is that you are calculating the SUMX (val_level2) and getting that result and adding it to your previous table so you are picking up all the 6 lines summing them up and then placing them has a value on your calculation, but since is with the entire table you get in every single line the same value.
Since DAX is a language that uses the context in the calculations you don't need to repeat the summarize and so on.
That is what I'm doing in my calculation I'm picking up for each line the the values of the corresponding market value by the value sum
At each level I get the correct value so for the group level I also get the correct sum because it's only the sum of the levels below, then I divide this by the sum at that level so this drills down no matter where you are.
3 Replies
Thank you Felix!
I'm just curious. do you know why my method won't work? the Val_level2 works fine if it's on GroupLevel2 scope, but not working if I want to use it when summarizing SubTable2 as a scalar expression?
- MFelix
Super User
Hi alwang ,
This has to do with the fact that you are reusing the subtable1 and the recalculating the values on top of it.
You subTable1 is returning the following values:
Has you can see the result is giving one line per grouplevel and then you pick up the values you need and make the calculations in this case for example for the Portfolio 1:
0.4* 3 + 0.6*2 = 1,2 + 1.2 = 2.4 so correct result has you expect.
If I know pick your second table and see the values you get:
What is happenning here is that you are calculating the SUMX (val_level2) and getting that result and adding it to your previous table so you are picking up all the 6 lines summing them up and then placing them has a value on your calculation, but since is with the entire table you get in every single line the same value.
Since DAX is a language that uses the context in the calculations you don't need to repeat the summarize and so on.
That is what I'm doing in my calculation I'm picking up for each line the the values of the corresponding market value by the value sum
At each level I get the correct value so for the group level I also get the correct sum because it's only the sum of the levels below, then I divide this by the sum at that level so this drills down no matter where you are.
