Forum Discussion

grubpot's avatar
grubpot
Frequent Visitor
6 years ago
Solved

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...
Need Help
  • rajulshah's avatar
    rajulshah
    6 years ago

    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's avatar
grubpot
Frequent Visitor
6 years ago

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%
v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity Support
6 years ago

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's avatar
    grubpot
    Frequent Visitor
    6 years ago

    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.

     

     

  • grubpot's avatar
    grubpot
    Frequent Visitor
    6 years ago

    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.