Forum Discussion

ricardobrubaker's avatar
ricardobrubaker
Frequent Visitor
8 years ago

SUM a Measure

Hello, 

 

I have the following dataset: 

 

 

MAPE UNITS = (sales-forecast)/Sales

% forecast= Forecast/Sum Forecast

WMAPE Units = MAPE UNITS * % FORECAST

 

The issue is that the Total of the Weighted MAPE  is not actually aggregating all the columns. I need the result to be (2.08+6.45+6.22+1.75........). 

 

Please help! 

 

Thanks! 

8 Replies

  • ccakjcrx's avatar
    ccakjcrx
    Resolver I
    8 years ago

    ricardobrubaker

    Hello!

    Here is a screenshot of my table with your data:

    I believe the solution is in the 'Mape Units' calculated column; if you set the expression up to change the NaN's to actual numbers, you'll get the outcome you are looking for. Here is the expression I used:

     

    Mape Units = if(ISERROR(abs((Sheet1[Sales]-Sheet1[Forecast Adjusted])/Sheet1[Sales])),0,abs((Sheet1[Sales]-Sheet1[Forecast Adjusted])/Sheet1[Sales]))

    I used abs to provide absolute values; otherwise I ended up with negative values in the columns--which I didn't see in your screenshot. 

    If needed, here is the link to my pbix file that contains the table:

    https://drive.google.com/open?id=1LOOSn-BzVHANAKBlPsCwa5Wnebj-Dviz

    • ricardobrubaker's avatar
      ricardobrubaker
      Frequent Visitor
      8 years ago

      Hi ccakjcrx 

      Thanks for your reply! Initially I thought you had it right but unfourtunately thats not working for me. 

       

      The reason that the Wmape Units field is summing correctly for you is because it is a 'Column' and not a 'Measure'. Columns can be easily added up but measures are not and thats were im having the issue. 

       

      I cannot change my WMAPE field from a Measure into a Column because of filtering. IF i make it a column, it doenst show the correct amount when I filter for anything. The same goes for the % Forecast field. 

      In the data set that you sent for example, if you filter for 2 categories only, the measurements are calculated incorrectly: 

       

      In this screenshot of your data set, % forecast is not showing the correct % of the filtered data. That means that the WMAPE Units is also incorrect. 

       

      To have the % forecast show the correct value after filtering, it needs to be a 'Measure' and not a 'Column' and  you need to use the following formula: 

      % Forecast for filters Measure = SUM(sheet1[FORECAST ADJUSTED])/CALCULATE(SUM(sheet1[FORECAST ADJUSTED]),ALLSELECTED('sheet1'))

      That gives you the following correct output: 




      As you can also see from the screenshot, the same formula created as a 'Column' gives an incorrect result. 

       

      Finally, we need to create the 'MEASURE' WMAPE Units to get the correct result. However, summing up measures is not possible:




      THanks for trying! Please help me out ive spent an entire morning on this one. 


      Ricardo  Brubaker 

       

       

       

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        8 years ago

        Hi ricardobrubaker,

         

        Try this formula please.

        Measure 2 =
SUMX (
    SUMMARIZE (
        'Table1',
        'Table1'[Year Week],
        'Table1'[GAMA],
        'Table1'[Sales],
        'Table1'[Forecast adjusted],
        "temp", [Mape Units] * [% forecast]
    ),
    IF ( ISERROR ( [temp] ), 0, [temp] )
)

        SUM_a_Measure

         

        Best Regards,

        Dale

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi ricardobrubaker,

     

    Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

     

    Best Regards!
    Dale