Average and distinct count

[unnijoy]

I have a table with Month Counyr and HC. I have country and HC will repeat for all month.

But i need to calculate the Average HC based on the distint count of HC.

So based onm the below table in need to get the average of two month like 454.75 ( By taking the Distinct HC ). But when i use the avg function i am getting 446.30. 

I need a formula that will give me 454.75.

Month	Country	HC
Jan- 01-2020	India	234
Jan- 01-2020	India	234
Jan- 01-2020	India	234
Jan- 01-2020	India	234
Jan- 01-2020	India	234
Jan- 01-2020	India	234
Jan- 01-2020	India	234
Jan- 01-2020	US	343
Jan- 01-2020	US	343
Jan- 01-2020	US	343
Jan- 01-2020	US	343
Jan- 01-2020	US	343
Jan- 01-2020	US	343
Feb-01-2020	India	456
Feb-01-2020	India	456
Feb-01-2020	India	456
Feb-01-2020	India	456
Feb-01-2020	India	456
Feb-01-2020	India	456
Feb-01-2020	India	456
Feb-01-2020	US	786
Feb-01-2020	US	786
Feb-01-2020	US	786
Feb-01-2020	US	786
Feb-01-2020	US	786
Feb-01-2020	US	786

[Anonymous]

Hi @unnijoy, Try this formula:

Avg = AVERAGEX(SUMMARIZECOLUMNS('Table'[Country],'Table'[HC],'Table'[Month]),'Table'[HC])

[harshnathani · Accepted solution]

HI @unnijoy ,

You can try

Average Of Country =
AVERAGEX (
    SUMMARIZE (
        'Table',
        'Table'[month],
        'Table'[Country],
        'Table'[HC],
        "DC", SUM ( 'Table'[HC] )
    ),
    'Table'[HC]
)

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[unnijoy]

Hi @harshnathani 

I tried the formula that you gave. But if HC is same for different month for same country it is taking the HC only once.

Eg: Jan 2020 India = 234

and in March 2020 India = 234. At that time the formula is taking 234 only once. Can you tell me wer i went wrong.

AVG_HC = AVERAGEX(SUMMARIZE(RAW,RAW[Month],RAW[Country],RAW[HC],"DC",SUM(RAW[HC])),RAW[HC])

Raw is TABLE name

[unnijoy]

The issue is fixed.

I change the formula to 

AVG_HC = AVERAGEX(SUMMARIZE(Table ,Table[Country],Table[HC]),Table[HC])

[amitchandak]

@unnijoy , try like

averageX(summarize(Table,Table[month],table[Country HC],"_1",[Distinct count measure]),[_1])
or
averageX(summarize(Table,table[Country HC],"_1",[Distinct count measure]),[_1])

[Daviejoe]

Will check out your blog post!