Forum Discussion
Summin a calculated weighted average in a Matrix Column subtotal
I have a simple calculated weighted average formua which works exactly as expected:
Totals = SUMX('Table', Table[Forecast_Value]*Table[DaysInMth])/SUMX('Table', Table[DaysInMth])
The Matrix displays the CWA for each category perfectly.
But when I add Column Subtotals in the Matrix Format Properties, the CWA is an average. I want the Column Subtotal to sum the CWA for the categories.
Is there an otpion I cannot see available in the Format Properties to do this or do I need to rewrite the expression in such a way to get what is needed? If so how do I get the sum of the CWA instead of an Average of the CWA? If I have to display this in 2 different Matrixes with 2 different formulas I will but to me that is not an optimal solution.
Thanks,
Hi cschuchert
You may refer to below measure.
Totals = SUMX ( 'TableA', TableA[Forecast_Value] * TableA[DaysInMth] ) / SUMX ( 'TableA', TableA[DaysInMth] )Subtotal = VAR a = SUMMARIZE ( TableA, TableA[Division], "b", [Totals] ) RETURN SUMX ( a, [b] )Regards,
Cherie
You are a genius!! I saw your post where it looked like I hadn't sent the data-I'm not sure what happened to my post with the excel data but it's there now.
I cannot thank you enough!! I'll have to research SUMMARIZE so I understand how to apply it to other visualizations.
Regards,
Carolyn
11 Replies
Can you send the result Matrix before and after the adition of Subtotals,
That way I can Help you.
See screenshots. Let me know if you only wanted the data.
AfterBeforeExpected Outcome
