Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
grubpot
Frequent Visitor

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

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.

 

SKUCustomerForecastOrderedAccuracy
AJoe Bloggs 1506020%
AJoe Bloggs 2405538%
AJoe Bloggs 330327%
AJoe Bloggs 460658%
AJoe Bloggs 570723%
AJoe Bloggs 680800%
AJoe Bloggs 79010011%
AJoe Bloggs 87055-21%
BJoe Bloggs 18032-60%
BJoe Bloggs 29065-28%
BJoe Bloggs 3507244%
BJoe Bloggs 4708014%
BJoe Bloggs 555609%
BJoe Bloggs 66055-8%
BJoe Bloggs 730327%
BJoe Bloggs 860658%
CJoe Bloggs 170723%
CJoe Bloggs 28032-60%
CJoe Bloggs 39065-28%
CJoe Bloggs 470723%
CJoe Bloggs 580800%
CJoe Bloggs 69055-39%
CJoe Bloggs 78032-60%
CJoe Bloggs 89065-28%

 

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

 

SKUForecastOrderedAccuracy
A4905196%
B495461-7%
C650473-27%

 

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

 

Total SKUsTotal in toleranceTolerance rate
3267%

 

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

Could anyone please advise?

 

Many than

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

View solution in original post

7 REPLIES 7
rajulshah
Super User
Super User

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.

forecast.png

 

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.

 

 

SKUForecastOrderedAccuracy
A4905196%
B495461-7%
C650473-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 )
)

Capture.PNG

 

For more details, please check the pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

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.

 

 

forecast.png

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors