topic Re: Average Discrepancy AVERAGE X blanks/zero creating difference in the average. in DAX Commands and Tips

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

Harishankar0592 — Mon, 09 Sep 2024 05:14:43 GMT

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

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

BeaBF — Mon, 09 Sep 2024 07:17:58 GMT

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

I'm here:

BBF

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

Harishankar0592 — Mon, 09 Sep 2024 07:56:22 GMT

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])),

DIVIDE(

SUMX(VALUES(Query1[Name]), [Pos Count]),

SUMX(VALUES(Query1[Name]), [Pos Count] + [Neg Count]),

) * 100

)

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

Harishankar0592 — Mon, 09 Sep 2024 08:12:55 GMT

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.

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

Anonymous — Tue, 10 Sep 2024 06:41:56 GMT

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.

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

Harishankar0592 — Thu, 19 Sep 2024 12:32:12 GMT

Thank You ....