Forum Discussion
Average Discrepancy AVERAGE X blanks/zero creating difference in the average.
Hello Friends,
Kindly find the below issue. I have an calculated column/Measure that returns below:
| 1 Count | 2 Count | Average 1 Score | Average 2 Score |
| 2 | 3 | 40 | 60 |
| 3 | 1 | 75 | 25 |
| 2 | 100 | ||
| 1 | 1 | 50 | 50 |
| 1 | 4 | 20 | 80 |
| 5 | 100 | 0 | |
| 2 | 2 | 50 | 50 |
| 3 | 1 | 75 | 25 |
If I take an average of column avg 2 score in excel using AVG(X:Y) its giving 48.7 and same numbers in PBI using AverageX its giving 45.16.
I have tried using +0 at the end, ISBLANK, ect. Kindly help me in this regard
Thank you very much BeaBF for your prompt reply.
For your question, here is the method I provided:
Here's some dummy data
“Table”
Check null values: Make sure all null values are handled correctly. You can use the IF function to replace the null value with zero. Create a measure:
Average2Score = IF( ISBLANK(SELECTEDVALUE('Table'[Average 2 Score])), 0, SELECTEDVALUE('Table'[Average 2 Score]) )Use
AVERAGEXto recalculate the average. Create a measure:Average = AVERAGEX( 'Table', 'Table'[Average2Score] )Here is the result.
Or you can simply adjust the [Average 2 Score] column to average.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank You ....
5 Replies
Thank you very much BeaBF for your prompt reply.
For your question, here is the method I provided:
Here's some dummy data
“Table”
Check null values: Make sure all null values are handled correctly. You can use the IF function to replace the null value with zero. Create a measure:
Average2Score = IF( ISBLANK(SELECTEDVALUE('Table'[Average 2 Score])), 0, SELECTEDVALUE('Table'[Average 2 Score]) )Use
AVERAGEXto recalculate the average. Create a measure:Average = AVERAGEX( 'Table', 'Table'[Average2Score] )Here is the result.
Or you can simply adjust the [Average 2 Score] column to average.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello BeaBF
Thank you for your response.
Kindly find the belowPos Count =CALCULATE(COUNTROWS(FILTER('Query1','Query1'[Is Question] = 1 &&'Query1'[Resp] = "Positive"Formula 2:Average Pos Score =COALESCE(IF(ISBLANK(SUMX(VALUES(Query1[Name]), [Pos Count])) ||ISBLANK(SUMX(VALUES(Query1[Name]), [Pos Count] + [Neg Count])),0,DIVIDE(SUMX(VALUES(Query1[Name]), [Pos Count]),SUMX(VALUES(Query1[Name]), [Pos Count] + [Neg Count]),0) * 100),0)On finding the average of Average 2 Score ? Average Pos Score in excel = AVG(60,25,100,50,80,0,50,25) is 48.7 %. If I use same average of Average 2 Score 60,25,100,50,80,0,50,25 I ended up with 45.16%. I dont know why 2 different answers for same set of numbers.
Thank You ....
