Summin a calculated weighted average in a Matrix Column subtotal

[cschuchert]

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,

[v-cherch-msft · Accepted solution]

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