Reply
Frequent Visitor
Posts: 7
Registered: ‎10-03-2018
Accepted Solution

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

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] )

1.png

 

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.

View solution in original post

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

View solution in original post


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

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

@cschuchert,

 

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

That way I can Help you.

 

 

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

After.jpgAfterBefore.jpgBeforeExpected Outcome.jpgExpected 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

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

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

DaysInMthDivisionForecast_DateForecast_PeriodForecast_TypeForecast_ValueGasFA GasFA McfeFA NGLFA OilFAVolumeMcfeNGLOilProduct_Type
312 LH8/29/2018 0:0010/1/2018 0:00Production11069800114730001147301106980004 Mcfe
312 LH8/29/2018 0:0010/1/2018 0:00Production11061711061711464900011464900001 Mcf
312 LH8/29/2018 0:0010/1/2018 0:00Production1300001313001302 Oil(Bbl)
312 LH8/29/2018 0:0010/1/2018 0:00Production0000 0 00003 NGL(Bbl)
313 MC8/29/2018 0:0010/1/2018 0:00Production003810000381000001 Mcf
313 MC8/29/2018 0:0010/1/2018 0:00Production0000031531500002 Oil(Bbl)
313 MC8/29/2018 0:0010/1/2018 0:00Production0000571057100003 NGL(Bbl)
313 MC8/29/2018 0:0010/1/2018 0:00Production000912600912600004 Mcfe
313 NL8/29/2018 0:0010/1/2018 0:00Production26176300274524002745242617630004 Mcfe
313 NL8/29/2018 0:0010/1/2018 0:00Production19771719771720901000020901000001 Mcf
313 NL8/29/2018 0:0010/1/2018 0:00Production893200091240912408932003 NGL(Bbl)
313 NL8/29/2018 0:0010/1/2018 0:00Production174300001795179500174302 Oil(Bbl)
314 SP8/29/2018 0:0010/1/2018 0:00Production191961000190754700190754719196100004 Mcfe
314 SP8/29/2018 0:0010/1/2018 0:00Production130149313014931311507000131150700001 Mcf
314 SP8/29/2018 0:0010/1/2018 0:00Production9343800089740089740093438003 NGL(Bbl)
314 SP8/29/2018 0:0010/1/2018 0:00Production958100009600960000958102 Oil(Bbl)
302 LH8/29/2018 0:0011/1/2018 0:00Production10827200112682001126821082720004 Mcfe
302 LH8/29/2018 0:0011/1/2018 0:00Production10819410819411260400011260400001 Mcf
302 LH8/29/2018 0:0011/1/2018 0:00Production1300001313001302 Oil(Bbl)
302 LH8/29/2018 0:0011/1/2018 0:00Production0000 0 00003 NGL(Bbl)
303 MC8/29/2018 0:0011/1/2018 0:00Production003722000372200001 Mcf
303 MC8/29/2018 0:0011/1/2018 0:00Production0000030630600002 Oil(Bbl)
303 MC8/29/2018 0:0011/1/2018 0:00Production0000556055600003 NGL(Bbl)
303 MC8/29/2018 0:0011/1/2018 0:00Production000889300889300004 Mcfe
303 NL8/29/2018 0:0011/1/2018 0:00Production25201400254158002541582520140004 Mcfe
303 NL8/29/2018 0:0011/1/2018 0:00Production19023019023019318600019318600001 Mcf
303 NL8/29/2018 0:0011/1/2018 0:00Production859500084320843208595003 NGL(Bbl)
303 NL8/29/2018 0:0011/1/2018 0:00Production170300001730173000170302 Oil(Bbl)
304 SP8/29/2018 0:0011/1/2018 0:00Production193449400200456800200456819344940004 Mcfe
304 SP8/29/2018 0:0011/1/2018 0:00Production131486313148631392232000139223200001 Mcf
304 SP8/29/2018 0:0011/1/2018 0:00Production9398400092522092522093984003 NGL(Bbl)
304 SP8/29/2018 0:0011/1/2018 0:00Production928700009534953400928702 Oil(Bbl)
312 LH8/29/2018 0:0012/1/2018 0:00Production10582100110610001106101058210004 Mcfe
312 LH8/29/2018 0:0012/1/2018 0:00Production10574610574611053600011053600001 Mcf
312 LH8/29/2018 0:0012/1/2018 0:00Production1200001212001202 Oil(Bbl)
312 LH8/29/2018 0:0012/1/2018 0:00Production0000 0 00003 NGL(Bbl)
313 MC8/29/2018 0:0012/1/2018 0:00Production003634000363400001 Mcf
313 MC8/29/2018 0:0012/1/2018 0:00Production0000029629600002 Oil(Bbl)
313 MC8/29/2018 0:0012/1/2018 0:00Production0000542054200003 NGL(Bbl)
313 MC8/29/2018 0:0012/1/2018 0:00Production000865800865800004 Mcfe
313 NL8/29/2018 0:0012/1/2018 0:00Production24375700239295002392952437570004 Mcfe
313 NL8/29/2018 0:0012/1/2018 0:00Production18390218390218167100018167100001 Mcf
313 NL8/29/2018 0:0012/1/2018 0:00Production831000079300793008310003 NGL(Bbl)
313 NL8/29/2018 0:0012/1/2018 0:00Production166600001674167400166602 Oil(Bbl)
314 SP8/29/2018 0:0012/1/2018 0:00Production189895800197882700197882718989580004 Mcfe
314 SP8/29/2018 0:0012/1/2018 0:00Production129094112909411370625000137062500001 Mcf
314 SP8/29/2018 0:0012/1/2018 0:00Production9255300092162092162092553003 NGL(Bbl)
314 SP8/29/2018 0:0012/1/2018 0:00Production878300009205920500878302 Oil(Bbl)
Community Support Team
Posts: 1,368
Registered: ‎07-25-2018

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

Hi @cschuchert

 

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.