Help with calclulation - so easy in Excel - no idea how to do this in Power BI

[grubpot]

Hi,

Hopefully someone has done something similar to this. I have searched but cannot find the solution.

I need to create a measure that returns the percentage of products where we have hit a decent level of forecast accuracy.

This is super simple in Excel.. but I am a bit lost recreating in Power BI!

I have a data set that has a load of products with a couple of hundred other columns of relevant data.

Simplest view is as below.

SKU	Customer	Forecast	Ordered	Accuracy
A	Joe Bloggs 1	50	60	20%
A	Joe Bloggs 2	40	55	38%
A	Joe Bloggs 3	30	32	7%
A	Joe Bloggs 4	60	65	8%
A	Joe Bloggs 5	70	72	3%
A	Joe Bloggs 6	80	80	0%
A	Joe Bloggs 7	90	100	11%
A	Joe Bloggs 8	70	55	-21%
B	Joe Bloggs 1	80	32	-60%
B	Joe Bloggs 2	90	65	-28%
B	Joe Bloggs 3	50	72	44%
B	Joe Bloggs 4	70	80	14%
B	Joe Bloggs 5	55	60	9%
B	Joe Bloggs 6	60	55	-8%
B	Joe Bloggs 7	30	32	7%
B	Joe Bloggs 8	60	65	8%
C	Joe Bloggs 1	70	72	3%
C	Joe Bloggs 2	80	32	-60%
C	Joe Bloggs 3	90	65	-28%
C	Joe Bloggs 4	70	72	3%
C	Joe Bloggs 5	80	80	0%
C	Joe Bloggs 6	90	55	-39%
C	Joe Bloggs 7	80	32	-60%
C	Joe Bloggs 8	90	65	-28%

This data set needs to have the forecast movement at product level, as per below..

SKU	Forecast	Ordered	Accuracy
A	490	519	6%
B	495	461	-7%
C	650	473	-27%

Then, most importantly I need the number of products that sit within a tolerance window, to achive the simple sum below.

Total SKUs	Total in tolerance	Tolerance rate
3	2	67%

I cannot for the life of me work out how to do this.

Could anyone please advise?

Many than

[rajulshah]

Hello @grubpot,

Could you please tell me how did you achieve Tolerance Rate in the last table?
Because I was able to achieve the previous table.

[grubpot]

Hi,

It is just the number of sku's in tolerance, divided by the total number of sku's

So 2/3  =67%

[rajulshah · Accepted solution]

Hello @grubpot,

Please create the following measures which will help you achieve your requirement:

Accuracy Measure = 
VAR Difference = SUM('Forecast Sales'[Ordered]) - SUM('Forecast Sales'[Forecast])
RETURN DIVIDE(Difference,SUM('Forecast Sales'[Forecast]))

Total in Tolerance = 
VAR SummarizedTable = SUMMARIZE('Forecast Sales','Forecast Sales'[SKU],"Accuracy Value",[Accuracy Measure])
RETURN COUNTX(FILTER(SummarizedTable,[Accuracy Value]<0),'Forecast Sales'[SKU])

Tolerance Rate = 
VAR TotalSKU = DISTINCTCOUNT('Forecast Sales'[SKU])
VAR ToleranceSKU = [Total in Tolerance]
RETURN DIVIDE(ToleranceSKU,TotalSKU)

Hope this helps.

[grubpot]

Hi,

Thanks for the reply.. it looks waaay more complicated than anything I could produce.

Just a question; how would i set the target for working out how many products were okay?

So for example, how could I set the target to be 15%, so in the below, 2 products are within target, but one of them is not.

SKU	Forecast	Ordered	Accuracy
A	490	519	6%
B	495	461	-7%
C	650	473	-27%

[v-frfei-msft]

Hi @grubpot ,

I have created a sample for your reference, we can create a measure as below to get the count of products.

Count = 
CALCULATE (
    DISTINCTCOUNT ( SKU[SKU] ),
    FILTER ( VALUES ( SKU[SKU] ), [Accuracy_] >= -0.15 )
)

For more details, please check the pbix as attached.

[grubpot]

Hi,

I found a workaround to count the sku's up correctly. So everything is working if I look at say 1 week of data.

Last problem now.. how would I update the formula to calculate the hit percentage by week?

The data is updated once per week, so I need to report the forecast accuracy by week, instead of as 1 big block of data.

[grubpot]

Hi,

Thanks for sending the update. However the calculations are not working for me.

I think it might be because I am getting the data from multiple sources.. they are coming from 3 different BEX queries.

The calculation for forecast accuracy is working fine.

The problem comes when I try to count the number of products that sit within tolerance.. the calculation is just not working.