Forum Discussion

Frequent Visitor

4 years ago

Solved

Sum of average

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. f...

Need Help

v-angzheng-msft

4 years ago

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.

W53_Sum of average_v2.pbix87 KB

v-angzheng-msft

Community Support

4 years ago

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.

W53_Sum of average.pbix87 KB

Boris_EmV

Frequent Visitor

4 years ago

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

Community Support

4 years ago

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.

W53_Sum of average_v2.pbix87 KB

Boris_EmV
Frequent Visitor
4 years ago
Many thanks for your support 🙂 It is working perfectly.