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

    • ricardobrubaker's avatar
      ricardobrubaker
      Frequent Visitor
      8 years ago

      Hi Dale,

       

      Thanks for the help, unfourtunately that is not working: 



      Also, your % forecast is incorrect as it should sum to 100%.

      For your first column, your % forecast should be 2571/2868 = 89%

       

      Thanks anyways! 

      Ricardo BRubaker

       

      • ccakjcrx's avatar
        ccakjcrx
        Resolver I
        8 years ago

        ricardobrubaker

         

        Hey!

         

        I wanted to post what I had in the event it might help. 

         

         

        Click here to get my pbix file. 

         

        Mape Units = ABS (
    	          DIVIDE (
                  ( SUM ( Sheet1[Sales] ) - SUM (       Sheet1[Forecast Adjusted] ) ),
                  SUM ( Sheet1[Sales] )
    	)
)
        Sum % Forecast = SUM ( Sheet1[Forecast Adjusted] )
        Calculate % Forecast = CALCULATE ( SUM ( Sheet1[Forecast Adjusted] ), ALL ( Sheet1 ) )
        % Forecast = DIVIDE ( [Sum %Forecast], [Calculate % Forecast] )
        WMAPE Units = [%Forecast] * [Mape Units]
        Correct Aggregate = SUMX(
        		SUMMERIZE(
        		Sheet1,
        		Sheet1[Day],
        		Sheet1[Sales],
        		Sheet1[Forecast Adjusted]
        		"temp", [WMAPE Units]
        		),
        		IF (ISERROR ([temp]), 0, [temp])
        		)	

        ****For the last measure, I manually typed it in here. I'm new to this board, and sometimes I have fits with getting 
        these msgs to format appropriately****

         

    • ccakjcrx's avatar
      ccakjcrx
      Resolver I
      8 years ago

      v-jiascu-msft

       

      Hey!

       

      That certainly worked for me. Thank you!

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

        You are welcome. ccakjcrx,

         

        Hi ricardobrubaker,

         

        That's one more question. You can try this formula for % forecast.

        % forecast =
SUM ( Table1[Forecast adjusted] )
    / CALCULATE ( SUM ( Table1[Forecast adjusted] ), ALLSELECTED ( Table1[GAMA] ) )

        SUM_a_Measure1

         

        Best Regards,

        Dale