Changing the level of calculation

[Anonymous]

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:

Month	SKU	Region	Type	Actuals	Forecast
December	30069	England	Franchise	200	100
December	30069	England	Equity	2000	1000
December	30069	Ireland	Franchise	75	2000
December	30069	Ireland	Equity	750	2100
December	40069	England	Franchise	1000	500
December	40069	England	Equity	1000	1700
December	40069	Ireland	Franchise	2100	450
December	40069	Ireland	Equity	750	500
December	50069	England	Franchise	299	450
December	50069	England	Equity	250	200
December	50069	Ireland	Franchise	600	450
December	50069	Ireland	Equity	750	900

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.

[Anonymous · Accepted solution]

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.