Forum Discussion

Harishankar0592's avatar
Harishankar0592
Regular Visitor
2 years ago
Solved

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 ...
  • Anonymous's avatar
    Anonymous
    1 year ago

    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's avatar
BeaBF
Icon for Super User rankSuper User
2 years ago

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

I'm here:

 

 

BBF

  • Harishankar0592's avatar
    Harishankar0592
    Regular Visitor
    2 years ago

    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
    )


  • Harishankar0592's avatar
    Harishankar0592
    Regular Visitor
    2 years ago

    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.