Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
Solved! Go to 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.
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.
Hi,
It is just the number of sku's in tolerance, divided by the total number of sku's
So 2/3 =67%
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.
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% |
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.
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.
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.
User | Count |
---|---|
116 | |
73 | |
60 | |
48 | |
48 |
User | Count |
---|---|
171 | |
122 | |
60 | |
59 | |
56 |