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

[da_heff]

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

[v-yalanwu-msft · Accepted solution]

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:

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.

[da_heff]

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

Greatly appreciated 🙂 

[da_heff]

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 Labels	Calls Offered	Calls Forecast	Forecast/Offered	Accuracy
2/12/2021	404	207.06	51.3%	51.3%
3/12/2021	282	176.61	62.6%	62.6%
4/12/2021	150	189	126.0%	79.4%

[amitchandak]

@da_heff ,

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

do you want 3rd one as 90 %

[da_heff]

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%