## Desktop

Frequent Visitor
Posts: 7
Registered: ‎10-03-2018

# 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,

Accepted Solutions
Community Support Team
Posts: 1,368
Registered: ‎07-25-2018

## Re: Summin a calculated weighted average in a Matrix Column subtotal

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor
Posts: 7
Registered: ‎10-03-2018

## Re: Summin a calculated weighted average in a Matrix Column subtotal

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

All Replies
Regular Visitor
Posts: 18
Registered: ‎10-02-2018

## Re: Summin a calculated weighted average in a Matrix Column subtotal

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

Frequent Visitor
Posts: 7
Registered: ‎10-03-2018

## Re: Summin a calculated weighted average in a Matrix Column subtotal

[ Edited ]

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

Frequent Visitor
Posts: 7
Registered: ‎10-03-2018

## Re: Summin a calculated weighted average in a Matrix Column subtotal

AfterBeforeExpected Outcome

Highlighted
Regular Visitor
Posts: 18
Registered: ‎10-02-2018

## Re: Summin a calculated weighted average in a Matrix Column subtotal

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,

Frequent Visitor
Posts: 7
Registered: ‎10-03-2018

## Re: Summin a calculated weighted average in a Matrix Column subtotal

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.

Community Support Team
Posts: 1,368
Registered: ‎07-25-2018

## Re: Summin a calculated weighted average in a Matrix Column subtotal

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

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

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor
Posts: 7
Registered: ‎10-03-2018

## Re: Summin a calculated weighted average in a Matrix Column subtotal

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:

Frequent Visitor
Posts: 7
Registered: ‎10-03-2018

## Re: Summin a calculated weighted average in a Matrix Column subtotal

 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)
Community Support Team
Posts: 1,368
Registered: ‎07-25-2018

## Re: Summin a calculated weighted average in a Matrix Column subtotal

It seems you haven't shown the data.

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.