Forum Discussion

Boris_EmV's avatar
Boris_EmV
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. 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!

 

Need Help
  • v-angzheng-msft's avatar
    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.

7 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      Boris_EmV
      Frequent Visitor
      4 years ago

      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's avatar
        lbendlin
        Icon for Super User rankSuper User
        4 years ago

        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)

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Icon for Community Support rankCommunity 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.

    • Boris_EmV's avatar
      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's avatar
        v-angzheng-msft
        Icon for Community Support rankCommunity 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.