Average Discrepancy AVERAGE X blanks/zero creating difference in the average.

[Harishankar0592]

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

[v-nuoc-msft · Accepted solution]

Hi @Harishankar0592 

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 AVERAGEX to 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.

[Harishankar0592 · Accepted solution]

Thank You .... 

[v-nuoc-msft · Accepted solution]

Hi @Harishankar0592 

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 AVERAGEX to 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.

[BeaBF]

@Harishankar0592 can you paste your current formula? and explain better the problem? 

I'm here:

BBF

[Harishankar0592]

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.

[Harishankar0592]

Hello @BeaBF 
Thank you for your response.
Kindly find the below 

Pos 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

)