CODEWITHSUNDEEP
powerbidax
Fatima
Fatima

Reputation: 495

calculate an average based on groups

I have data similar to this table

root    sub   questions         scores
C       C.1     C.1.1              0
C       C.1     C.1.2             30
C       C.1     C.1.3             100
C       C.1     C.1.4              0
C       C.1     C.1.5              70
C       C.2     C.2.1              0
C       C.2     C.2.2              0
C       C.2     C.2.3              90
C       C.2     C.2.4              0
C       C.3     C.3.1             100
C       C.3     C.3.2              0
C       C.3     C.3.3              40
C       C.4     C.4.1               0
C       C.4     C.4.2              59
C       C.4     C.4.3              30
C       C.4     C.4.4              90
C       C.5     C.5.1              20
C       C.5     C.5.2               0

and I used (zoomcharts advanced donut visual) chart to visualize the data with drill-down, and I found that the average of the root is calculated based on overall rows, not based on the group.

here is an example for more explain the chart takes the average of root C like this

(0+30+100+0+70+0+0+90+0+100+40+0+59+30+90+20+0)/18= 34.94

and I want to take it by the groups first, and the result is different too

c.1=40

c.2=22.5

c.3=46.66

c.4=44.75

c.5=10

(c.1+c.2+c3+c.4+c.5)/5=32.7 and on the chart, C = 34.94 means it takes the average by overall is there any chart or way to make it take the average for the groups with drill down?

Upvotes: 0

Views: 79

Answers (1)

StelioK
StelioK

Reputation: 1781

Something like this will work: 

Average Per Sub Group :=
CALCULATE (
    AVERAGEX (
        SUMMARIZE ( 'Table', 'Table'[Sub], "Total", SUM ( 'Table'[scores] ) ),
        [Total]
    )
)

How this works:

  1. SUMMARIZE will generate a table which will aggregate all scores grouping by 'Table'[sub]
  2. AVERAGEX will iterate through each row in this table (SUMMARIZE), taking the average at the 'Table'[sub] level

I hope it helps!

Upvotes: 1

Related Questions