Gap of Data not working

[nataliesmiy1357]

Hello!  My gap/difference measure isn't working correctly.  

This is my raw data:

My page is bringing in the average per Line (so taking the average of shift 1 and 2) 

I have a GAP of OR forumla that is: 

Gap of OR = 'OR Report'[Line Result]  - 'OR Report'[Planned OR%]

But when I bring that Gap value into a graph, this is what I get:

But the average of Line Result  ( 98.2 + 97.2 + 95.8 /3 = 97.07) and the planned result = 96, so the difference/gap SHOULD be 97.07-96 = 1.07

What am I doing wrong?

[v-stephen-msft]

 Hi @nataliesmiy1357 ,

I tried to reproduced it but failed. I used AVERAGE function. Here's the results. My Line Result and Planned OR% are both columns.

Please provide us with more detailed information, thanks.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

and the planned result = 96

well, not exactly.  it is 96*5/6 = 80.  Hence the difference is 17.07% rather than 1.07%

[nataliesmiy1357]

That makes sense - so how can I create the DAX to not consider the null/N/a values?  So taking the average of the actual data:  96*5 / 5 actual values.

[lbendlin]

So you would filter out the associated 98.2% line result as well?

[nataliesmiy1357]

No - I would want that included.

[lbendlin]

I feared as much.  I would call that "lying to one self". Can't/won't help you with that.

[nataliesmiy1357]

okay.... what if I don't include it?