Search code examples
powerbidaxaverage

How to calculate the average of the average in PowerBI using DAX?

enter image description here

Hi everyone,

I have a table which record the percentage of attendance as shown in the screenshot. I want to calculate the average attendance rate of all the events by following method:

average attendance rate for event 1 = (12% + 7% + 15%)/3 = 11.33%

average attendance rate for event 2 = (22% + 37%)/2 = 29.5%

average attendance rate for event 3 = 10%

average attendance rate for event 2 = (14% + 15%)/2 = 14.5%

Final result:

Total average attendance rate for all events = (11.33% + 29.5% + 10% + 14.5%)/4 = 16.33%

The final output that I want is 16.33%, couldn't figure out how to use DAX to get the final output that I want. The final output will be used in Card visualization.

I'm aware that I can put the Attendance rate column directly into card visualization and get the average attendance rate which is 16.5%, but this is not the way that I'm interested. Any help will be greatly appreciated!

Event   Attendance rate
event 1 12.00%
event 1 7.00%
event 1 15.00%
event 2 22.00%
event 2 37.00%
event 3 10.00%
event 4 14.00%
event 4 15.00%
Solution
    1. The inner CALCULATE(AVERAGE()) computes aver for each event value (event1, event2 ...)
    2. AVERAGEX() computes aver among 1.
      cardMeasure=    
  AVERAGEX(
           VALUES(tbl[Event])
           ,CALCULATE(AVERAGE(tbl[Attendance rate]))
 )