Forum Discussion

AgustinBrocardo's avatar
AgustinBrocardo
Frequent Visitor
4 years ago

average calculation problem

Hi!

I´m having a problem but i cannot find the cause.

 

I make a survey to my customers for them to rate my service from 1 to 5 and some of them may not respond, as you can see in the capture.

 

If i calculate the average from the rating column in excel and in power bi the rresult if the same. But if i filter in excel, for example by 3Q21 and calculate the average of those columns, the result is different from the average calculated by power bi when the slicer of 3Q21 is applied.

 

6 Replies

  • AgustinBrocardo's avatar
    AgustinBrocardo
    Frequent Visitor
    4 years ago

    Yes! Excel calculates an average of 4.39 while power bi an average of 4.42

     

    the data in the capture of the post was an example, i would attach the xlsx file but i doesn´t let me.

     

    • AlexisOlson's avatar
      AlexisOlson
      Super User
      4 years ago

      This screenshot does not align with your example table. I only see two rows in your sample data for that quarter (one for each Client), so it's unclear to me why neither of the averages is a simple 3.5.

  • v-cazheng-msft's avatar
    v-cazheng-msft
    Community Support
    4 years ago

    Hi AgustinBrocardo 

     

    May I know is your issue resolved? If your issue still exists, could you please show us how you calculate the average in Power BI? Is there any Measure you create to get the Rating on mean?

     

    Best Regards,

    Community Support Team _ Caiyun

     

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

    • AgustinBrocardo's avatar
      AgustinBrocardo
      Frequent Visitor
      4 years ago

      hi v-cazheng-msft 

       

      We tried solving the problem with my team too. The average is a simple calculation made by the visual by selecting "average" when showing values, there is no measure.

       

      We couldn´t find the cause of the problem, we created a new report and started over. The second time i didn´t make any change to the data in power query and this time the average was calculated correctly. It seems the problem was in the query steps, which just was unpivoting some columns. 

       

      I still don´t understand why unpivoting some columns could cause this problem but i worked around it.