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

[Anonymous]

@cschuchert,

Can you send the result Matrix before and after the adition of Subtotals,

That way I can Help you.

[cschuchert]

See screenshots. Let me know if you only wanted the data.

[cschuchert]

AfterBeforeExpected Outcome

[Anonymous]

Hello @cschuchert,

Now I understand what You need.

The aswer is pretty simple after you discover, and you will be making the same fix a lot of times =).

The DAX is doing the same measure you create for the totals, It consider the total line as another row in the table, that way the results in error for you that want the totals of lines.

For this treatment you will need to tell DAX that if you filter the column "Product_Type" you want the sum of row.

You will use IF plus HASONEFILTER or ISFILTERED, you chose one or another.

Like:

Measure = IF ( ISFILTERED ( ColumnName[Product_Type] ) ; Sum(TableName[Category] ; SUMX ( 'Table', Table[Forecast_Value] * Table[DaysInMth]) / SUMX ( 'Table', Table[DaysInMth] ) )

Cheers,

[cschuchert]

The Matrix in my screenshots is only filtered by the Quarter. I also have multiple slicers on the page for the Forecast week, Division, ProductType and Quarter used in another Matrix on the same page. The Matrix using the meaure is to get a CWA for each Quarter; all filters work on both Matrixes.

If I filter this to one Division the Total is correct but if all Divisions are selected the Total is the Average.

What exactly is  Sum(TableName[Category] doing in the measure example? I understand an IF statement needs a True/False but all I really want is for this measure to Sum the values used in my original measure.

[v-cherch-msft]

Hi @cschuchert

You may have a look at below posts. If you need further help, please share some simplified data sample.

How to Get Your Question Answered Quickly

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Regards,

Cherie

[cschuchert]

Thank you-I did find that post yesterday and can get the first part of the Measure I'm stuck on the false portion though. Here is the data for this app:

[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

[cschuchert · Accepted solution]

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

[v-cherch-msft]

Hi @cschuchert

It seems you haven't shown the data.

Regards,

Cherie

[cschuchert]

DaysInMth	Division	Forecast_Date	Forecast_Period	Forecast_Type	Forecast_Value	Gas	FA Gas	FA Mcfe	FA NGL	FA Oil	FAVolume	Mcfe	NGL	Oil	Product_Type
31	2 LH	8/29/2018 0:00	10/1/2018 0:00	Production	110698	0	0	114730	0	0	114730	110698	0	0	04 Mcfe
31	2 LH	8/29/2018 0:00	10/1/2018 0:00	Production	110617	110617	114649	0	0	0	114649	0	0	0	01 Mcf
31	2 LH	8/29/2018 0:00	10/1/2018 0:00	Production	13	0	0	0	0	13	13	0	0	13	02 Oil(Bbl)
31	2 LH	8/29/2018 0:00	10/1/2018 0:00	Production	0	0	0	0		0		0	0	0	03 NGL(Bbl)
31	3 MC	8/29/2018 0:00	10/1/2018 0:00	Production	0	0	3810	0	0	0	3810	0	0	0	01 Mcf
31	3 MC	8/29/2018 0:00	10/1/2018 0:00	Production	0	0	0	0	0	315	315	0	0	0	02 Oil(Bbl)
31	3 MC	8/29/2018 0:00	10/1/2018 0:00	Production	0	0	0	0	571	0	571	0	0	0	03 NGL(Bbl)
31	3 MC	8/29/2018 0:00	10/1/2018 0:00	Production	0	0	0	9126	0	0	9126	0	0	0	04 Mcfe
31	3 NL	8/29/2018 0:00	10/1/2018 0:00	Production	261763	0	0	274524	0	0	274524	261763	0	0	04 Mcfe
31	3 NL	8/29/2018 0:00	10/1/2018 0:00	Production	197717	197717	209010	0	0	0	209010	0	0	0	01 Mcf
31	3 NL	8/29/2018 0:00	10/1/2018 0:00	Production	8932	0	0	0	9124	0	9124	0	8932	0	03 NGL(Bbl)
31	3 NL	8/29/2018 0:00	10/1/2018 0:00	Production	1743	0	0	0	0	1795	1795	0	0	1743	02 Oil(Bbl)
31	4 SP	8/29/2018 0:00	10/1/2018 0:00	Production	1919610	0	0	1907547	0	0	1907547	1919610	0	0	04 Mcfe
31	4 SP	8/29/2018 0:00	10/1/2018 0:00	Production	1301493	1301493	1311507	0	0	0	1311507	0	0	0	01 Mcf
31	4 SP	8/29/2018 0:00	10/1/2018 0:00	Production	93438	0	0	0	89740	0	89740	0	93438	0	03 NGL(Bbl)
31	4 SP	8/29/2018 0:00	10/1/2018 0:00	Production	9581	0	0	0	0	9600	9600	0	0	9581	02 Oil(Bbl)
30	2 LH	8/29/2018 0:00	11/1/2018 0:00	Production	108272	0	0	112682	0	0	112682	108272	0	0	04 Mcfe
30	2 LH	8/29/2018 0:00	11/1/2018 0:00	Production	108194	108194	112604	0	0	0	112604	0	0	0	01 Mcf
30	2 LH	8/29/2018 0:00	11/1/2018 0:00	Production	13	0	0	0	0	13	13	0	0	13	02 Oil(Bbl)
30	2 LH	8/29/2018 0:00	11/1/2018 0:00	Production	0	0	0	0		0		0	0	0	03 NGL(Bbl)
30	3 MC	8/29/2018 0:00	11/1/2018 0:00	Production	0	0	3722	0	0	0	3722	0	0	0	01 Mcf
30	3 MC	8/29/2018 0:00	11/1/2018 0:00	Production	0	0	0	0	0	306	306	0	0	0	02 Oil(Bbl)
30	3 MC	8/29/2018 0:00	11/1/2018 0:00	Production	0	0	0	0	556	0	556	0	0	0	03 NGL(Bbl)
30	3 MC	8/29/2018 0:00	11/1/2018 0:00	Production	0	0	0	8893	0	0	8893	0	0	0	04 Mcfe
30	3 NL	8/29/2018 0:00	11/1/2018 0:00	Production	252014	0	0	254158	0	0	254158	252014	0	0	04 Mcfe
30	3 NL	8/29/2018 0:00	11/1/2018 0:00	Production	190230	190230	193186	0	0	0	193186	0	0	0	01 Mcf
30	3 NL	8/29/2018 0:00	11/1/2018 0:00	Production	8595	0	0	0	8432	0	8432	0	8595	0	03 NGL(Bbl)
30	3 NL	8/29/2018 0:00	11/1/2018 0:00	Production	1703	0	0	0	0	1730	1730	0	0	1703	02 Oil(Bbl)
30	4 SP	8/29/2018 0:00	11/1/2018 0:00	Production	1934494	0	0	2004568	0	0	2004568	1934494	0	0	04 Mcfe
30	4 SP	8/29/2018 0:00	11/1/2018 0:00	Production	1314863	1314863	1392232	0	0	0	1392232	0	0	0	01 Mcf
30	4 SP	8/29/2018 0:00	11/1/2018 0:00	Production	93984	0	0	0	92522	0	92522	0	93984	0	03 NGL(Bbl)
30	4 SP	8/29/2018 0:00	11/1/2018 0:00	Production	9287	0	0	0	0	9534	9534	0	0	9287	02 Oil(Bbl)
31	2 LH	8/29/2018 0:00	12/1/2018 0:00	Production	105821	0	0	110610	0	0	110610	105821	0	0	04 Mcfe
31	2 LH	8/29/2018 0:00	12/1/2018 0:00	Production	105746	105746	110536	0	0	0	110536	0	0	0	01 Mcf
31	2 LH	8/29/2018 0:00	12/1/2018 0:00	Production	12	0	0	0	0	12	12	0	0	12	02 Oil(Bbl)
31	2 LH	8/29/2018 0:00	12/1/2018 0:00	Production	0	0	0	0		0		0	0	0	03 NGL(Bbl)
31	3 MC	8/29/2018 0:00	12/1/2018 0:00	Production	0	0	3634	0	0	0	3634	0	0	0	01 Mcf
31	3 MC	8/29/2018 0:00	12/1/2018 0:00	Production	0	0	0	0	0	296	296	0	0	0	02 Oil(Bbl)
31	3 MC	8/29/2018 0:00	12/1/2018 0:00	Production	0	0	0	0	542	0	542	0	0	0	03 NGL(Bbl)
31	3 MC	8/29/2018 0:00	12/1/2018 0:00	Production	0	0	0	8658	0	0	8658	0	0	0	04 Mcfe
31	3 NL	8/29/2018 0:00	12/1/2018 0:00	Production	243757	0	0	239295	0	0	239295	243757	0	0	04 Mcfe
31	3 NL	8/29/2018 0:00	12/1/2018 0:00	Production	183902	183902	181671	0	0	0	181671	0	0	0	01 Mcf
31	3 NL	8/29/2018 0:00	12/1/2018 0:00	Production	8310	0	0	0	7930	0	7930	0	8310	0	03 NGL(Bbl)
31	3 NL	8/29/2018 0:00	12/1/2018 0:00	Production	1666	0	0	0	0	1674	1674	0	0	1666	02 Oil(Bbl)
31	4 SP	8/29/2018 0:00	12/1/2018 0:00	Production	1898958	0	0	1978827	0	0	1978827	1898958	0	0	04 Mcfe
31	4 SP	8/29/2018 0:00	12/1/2018 0:00	Production	1290941	1290941	1370625	0	0	0	1370625	0	0	0	01 Mcf
31	4 SP	8/29/2018 0:00	12/1/2018 0:00	Production	92553	0	0	0	92162	0	92162	0	92553	0	03 NGL(Bbl)
31	4 SP	8/29/2018 0:00	12/1/2018 0:00	Production	8783	0	0	0	0	9205	9205	0	0	8783	02 Oil(Bbl)