Forum Discussion

unnijoy's avatar
unnijoy
Icon for Post Prodigy rankPost Prodigy
6 years ago
Solved

Average and distinct count

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.

 

 

MonthCountryHC
Jan- 01-2020India234
Jan- 01-2020India234
Jan- 01-2020India234
Jan- 01-2020India234
Jan- 01-2020India234
Jan- 01-2020India234
Jan- 01-2020India234
Jan- 01-2020US343
Jan- 01-2020US343
Jan- 01-2020US343
Jan- 01-2020US343
Jan- 01-2020US343
Jan- 01-2020US343
Feb-01-2020India456
Feb-01-2020India456
Feb-01-2020India456
Feb-01-2020India456
Feb-01-2020India456
Feb-01-2020India456
Feb-01-2020India456
Feb-01-2020US786
Feb-01-2020US786
Feb-01-2020US786
Feb-01-2020US786
Feb-01-2020US786
Feb-01-2020US786
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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!

     

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      unnijoy
      Icon for Post Prodigy rankPost Prodigy
      6 years ago

      Hi Anonymous 

       

      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's avatar
        unnijoy
        Icon for Post Prodigy rankPost Prodigy
        6 years ago

        The issue is fixed.

         

        I change the formula to 

        AVG_HC = AVERAGEX(SUMMARIZE(Table ,Table[Country],Table[HC]),Table[HC])
  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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])
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi unnijoy, Try this formula:

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