Forum Discussion

DeeMag's avatar
DeeMag
Helper II
5 years ago
Solved

Average

Hi,   Can you help me why Power BI shows different average to correct average. 1st screen shot shows excel average to be 271.6  2nd screen shot shows Power BI where average for column shows as 28...
Need Help
  • DeeMag's avatar
    DeeMag
    5 years ago

    I was able to solve that by adding addin allselected on top value function. Thanks.

amitchandak's avatar
amitchandak
Super User
5 years ago

DeeMag , If you have used a formula

Sum(A)/Sum(B)

 

Power bi will recalculate the grand total by summing up the A and B

 

In the case of excel, it is a simple avg of rows

 

in power bi you can get that like

 

averagex(Values(Table[Agent name]), Divide(Sum(Table[A]), Sum(Table[B])))

 

Where Table[Agent name] is column on which data is grouping for avg

  • DeeMag's avatar
    DeeMag
    Helper II
    5 years ago

    Thanks Amit, i will try the way you suggested.

     

  • DeeMag's avatar
    DeeMag
    Helper II
    5 years ago

    Hi Amit,

     

    The value turns out to be much lower using Averagex and Values.

    can you suggest some other work around else please?

    Measure = SUMX(VALUES(Cus_AgentPerformance[AgentName]),DIVIDE([Total],sumx(Cus_AgentPerformance,Cus_AgentPerformance[Inbound Calls])))
    Measure 2 = AVERAGEX(VALUES(Cus_AgentPerformance[AgentName]),DIVIDE([Total],sumx(Cus_AgentPerformance,Cus_AgentPerformance[Inbound Calls])))

    Thank you.