Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Changing the level of calculation

Hi, 

Im attempting to create an interactable table for forecast accuracy. 

Not sure how best to explain it, but I have the following data set:

MonthSKURegionTypeActualsForecast
December30069EnglandFranchise200100
December30069EnglandEquity20001000
December30069IrelandFranchise752000
December30069IrelandEquity7502100
December40069EnglandFranchise1000500
December40069EnglandEquity10001700
December40069IrelandFranchise2100450
December40069IrelandEquity750500
December50069EnglandFranchise299450
December50069EnglandEquity250200
December50069IrelandFranchise600450
December50069IrelandEquity750900

 

From this dataset I can calculate the BIAs [ Forecast - Actuals ], Error ([ABS (BIAs) ]and MAPE [ 1 - ( ABS ( Forecast - Actuals ) / Actuals ) ] by row.

I have been able to create measures and columns that provide the numbers from this lowest level and then create totals and subtotals based of these.

However, I wish that the SKU be the lowest level (If that makes sense), whilst keeping the filtering options available. 

Essentially, the table will take each SKU that is selected (or ALL if none) apply the other filters, and calculate BIAs and Error for each SKU at that level, Error being the absolute value of BIAs per SKU. 

Then when calculating the total of the combined SKUs it will sum the Error values (instead of deriving it from BIAs). 

I have 4 'tests' that have excel based examples on:

Note - the arrow on Test 4 is just a direction of how the calculations should process.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

See my example file here: Power BI Test File 

 

Thanks in advance.

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

     

    According to your description, here are my steps you can follow as a solution.

    (1) My test data is the same as yours.

    (2) We can create a new measure. 

    new .m error abs =
IF (
    ISINSCOPE ( 'Example Data'[SKU] ),
    [.m Error Abs],
    SUMX ( VALUES ( 'Example Data'[SKU] ), [.m Error Abs] )
)

    (3) Then the result is as follows.

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

     

    According to your description, here are my steps you can follow as a solution.

    (1) My test data is the same as yours.

    (2) We can create a new measure. 

    new .m error abs =
IF (
    ISINSCOPE ( 'Example Data'[SKU] ),
    [.m Error Abs],
    SUMX ( VALUES ( 'Example Data'[SKU] ), [.m Error Abs] )
)

    (3) Then the result is as follows.

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.