Sum of average

[Boris_EmV]

Hello,

I will need your help to get sum of the averages. The masures are using average values (percentages) and total in matrix is showing it as average and this is normal. I would like to e.g. for volumes to be sum by location and by datatypesub. Maybe the masures can also be optimized (AVERAGEX) for this purpose, because some of them are refer to Raw Material, others are PFL filtered and finally mixed in final measure PFL Volume c.b.

Note: datatypesub is only for PFL, grind is only for Raw Material therefore tried to remove filters when using it in a matrix where the rows are coming from PFL. For the moment it is working well with that.

Here is the link to the power bi file:

https://www.dropbox.com/s/yunwdjwrj4j2lc5/Sample.pbix?dl=0

Thanks in advance!

[v-angzheng-msft]

Hi, @Boris_EmV 

Try to create a measure like this:

Measure_new = 
var _new=SUMMARIZE('Sheet1',[datatypesub],[location_desc])

return IF(HASONEVALUE('Sheet1'[datatypesub]),[PFL Volume c.b.],SUMX(_new,[PFL Volume c.b.])
)

 Result:

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Boris_EmV]

Great. It is what I need.

The only thing is that the total by row is still showing average (e.g. Bran = 104,376.73 instead of 104,223.11)

I tried measure SUMX(VALUES(Sheet1[location_desc]), [PFL Volume c.b.]) which is giving the correct result by row, but i am not be able to combine with your measure.

[v-angzheng-msft · Accepted solution]

Hi, @Boris_EmV 
Try this:

Measure_new = 
var _t1=SUMMARIZE('Sheet1',[datatypesub],[location_desc])
var _t2=SUMMARIZE(ALLSELECTED('Sheet1'),[datatypesub],[location_desc])

var _rowTotal=SUMX(_t1,[PFL Volume c.b.])
var _columnTotal=    SUMX(FILTER(_t2,[datatypesub]=MAX('Sheet1'[datatypesub])),[PFL Volume c.b.])
var _if=
SWITCH(TRUE(),
    ISINSCOPE('Sheet1'[datatypesub])&&ISINSCOPE('Sheet1'[location_desc]),[PFL Volume c.b.],
    Not(ISINSCOPE('Sheet1'[datatypesub]))&&ISINSCOPE('Sheet1'[location_desc]),_rowTotal,
    ISINSCOPE('Sheet1'[datatypesub])&&Not(ISINSCOPE('Sheet1'[location_desc])),_columnTotal,
    SUMX(_t2,[PFL Volume c.b.])
    
)
return _if

Result:

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Boris_EmV]

Many thanks for your support 🙂 It is working perfectly.

[lbendlin]

Thank you for providing the sample PBIX.  

You have a lot of measure nesting going on.  As far as I can tell the actual computation will be 

AVERAGE(Sheet1[Yield_ds_PFL(AVERAGE)])
* 
CALCULATE(AVERAGE(Sheet1[1-MoistureRM(AVERAGE)]), ALL(Sheet1[datatypesub])) --removing filters, it is Raw Material (RM)
/ 
AVERAGE(Sheet1[1-MoisturePFL(AVERAGE)])
 * 
CALCULATE(
SUM(Sheet1[GrindRM(SUM)]),
ALL(Sheet1[datatypesub])) --removing filters, it is only Raw Material type

That's rather difficult to debug without context.  But maybe you can see from that if it calculates what you expected or not.

[Boris_EmV]

Hi, @lbendlin 

Thanks for your reply, tried it but still not getting the result. I am wondering if there is any way of transforming the data somehow to solve it.

[lbendlin]

yes, start over and create a single measure that computes your value.  Try to avoid nesting measures if you can.

It helps if you have a clear idea of the expected outcome for that measure. I often write these down to get the clarity I need (and I often discover that I was using the wrong formulations/assumptions etc)