Average of measure grouped by column

[modverre]

Hi everyone,

I want to create a graph showing the average of the measure Has_Licence for each country, so basically sum(Has_Licence) / distinctcount(Account). 

I've tried a bunch of different formulas I've found online, but they all simply return 1 for the accounts that have a licence and blank for the rest, whereas I want an average per country.

Relatively new to DAX and would be grateful if someone could help me. Thank you!

[tamerj1 · Accepted solution]

Hi @modverre 
Please refer to attached sample file with the solution

Desired Outcome = 
AVERAGEX ( 
    CALCULATETABLE (
        VALUES ( 'Table'[Account] ),
        ALLEXCEPT ( 'Table', 'Table'[Country] )
    ),
    [Measure Has_Licence]
)

[modverre]

This worked! Thank you so much @tamerj1 and everyone else who contributed solutions 🙂

[v-yueyunzh-msft]

Hi , @modverre 

According to your description, you want to "Average of measure grouped by column".

Here are the steps you can refer to :
(1)This is my test data:

(2)I create a measure like your Has_Licence:

Has_Licence = SUM('Table'[License])

(3)Then we can click "New Measure" to create a measure :

Average = var _t = SUMMARIZE(ALLSELECTED('Table') ,'Table'[Country],'Table'[Account],"Has_Licence" , [Has_Licence])
var _cur_country = MAX('Table'[Country])
var _sum =SUMX( FILTER(_t , [Country] = _cur_country) , [Has_Licence])
var _count =COUNTROWS(DISTINCT( SELECTCOLUMNS( FILTER(_t , [Country] = _cur_country) , "Account",[Account])))
return
DIVIDE(_sum,_count)

(4)Then we can put the fields on the visual and we can meet your need, the result is as follows:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[Greg_Deckler]

@modverre This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.