cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
da_heff
New Member

create a measure to calculate % difference in 2 fields in a row of data

Hi all,

I have a dataset that includes columns for forecast volume and actual calls received.  As a KPI, I need to know the % accuracy of the forecast .

eg:

Forecast   Actual   Accuracy

100           100        100%

100             90          90%

90              100         90% 

How do I write the query to show the % difference? 

The intent is to then link the daily % into a dashboard to reflect the level of accuracy and support business decision making

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

Hi, @da_heff ;

You could create a measure as follows:

Measure = 
 var _div1=DIVIDE(SUM('Table'[Forecast]),SUM('Table'[Actual]))
 var _div2=DIVIDE(SUM('Table'[Actual]),SUM('Table'[Forecast]))
 return IF(_div1<0||_div1>1,_div2,_div1)

The final output is shown below:

vyalanwumsft_0-1646619868876.png


Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-yalanwu-msft
Community Support
Community Support

Hi, @da_heff ;

You could create a measure as follows:

Measure = 
 var _div1=DIVIDE(SUM('Table'[Forecast]),SUM('Table'[Actual]))
 var _div2=DIVIDE(SUM('Table'[Actual]),SUM('Table'[Forecast]))
 return IF(_div1<0||_div1>1,_div2,_div1)

The final output is shown below:

vyalanwumsft_0-1646619868876.png


Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks @v-yalanwu-msft , that is great and meets the needs perfectly.

 

Greatly appreciated 🙂 

da_heff
New Member

To calculate the accuracy in EXCEL, this works to identify the figure: =IF(D2<=1,D2,IF(D2>1,B2/C2))

 

The objective is to move our dashboards into Power BI to provide greater flexibility and reporting agility.

 

An example dataset:

 

Row LabelsCalls OfferedCalls ForecastForecast/OfferedAccuracy
2/12/2021404207.0651.3%51.3%
3/12/2021282176.6162.6%62.6%
4/12/2021150189126.0%

79.4%

amitchandak
Super User
Super User

@da_heff ,

a new measure = divide(sum(Table[Actual]), sum(Table[Forecast]))

 

do you want 3rd one as 90 %



Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Learn Power BI P&L Statement || Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s ||
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!!! How to get Quick Help !!
Resources | Master Power BI !! Expertise Power BI !! Power BI For Tableau User !!

Hi @amitchandak 

Yes, the objective is to get the 3rd response as 90% (as in this example) as well.

In each of the 2 90% examples, the forecast was inaccurate, but the % cannot be negative or greater than 100%

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.