CODEWITHSUNDEEP
powerbiaggregatedax
Zabman
Zabman

Reputation: 117

Get aggregate of aggregate by group PowerBI

I have a data set that is aggregated by supplier month/name/location/division and shows the mean and the count of each group as follows:

Month name location division mean count
Dec Globalf GC Sales 4 2
Dec localF GC Sales 5.44 27
Dec Globalf GC Purchasing 0.00 2
Dec Globalf NC Sales 3 1

I would like to create a dax formula that gives me the average of the current group ie:

Month location division mean count
Dec GC Sales 5.341 29
Dec GC Purchasing 0.00 2
Dec NC Sales 3 1

Average of averages isn't what I am looking for as AVERAGEX gives me an incorrect result. I have also tried multiplying but this does it on the grouped value so is giving a wrong result:

grp_average = SUM('tbl'[mean]) * SUM('tbl'[count])

Is anyone able to help me?

Upvotes: 0

Views: 27

Answers (1)

sergiom
sergiom

Reputation: 4877

It looks like you need a weighted average

grp_average = DIVIDE ( SUMX( 'tbl', 'tbl'[mean] * 'tbl[count] ), SUM( 'tbl'[count] ) )

Upvotes: 1

Related Questions