cancel
Showing results for
Did you mean:
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
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:

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.

5 REPLIES 5
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:

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.

New Member

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

Greatly appreciated 🙂

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 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%
Super User

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

do you want 3rd one as 90 %

New Member

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%