SUM a Measure

[ricardobrubaker]

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! 

[v-jiascu-msft]

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

[ccakjcrx]

@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]

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]

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] )
)

Best Regards,

Dale

[ccakjcrx]

@v-jiascu-msft

Hey!

That certainly worked for me. Thank you!

[v-jiascu-msft]

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] ) )

Best Regards,

Dale

[ricardobrubaker]

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]

@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****