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