Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Distinct average by two categories

Hello. (sorry for the earlier incomplete post). I am new to power BI. I would like a measure to do a Distinct Average of a column using two (or more) categories for grouping.   ID         DATE     ...
  • Anonymous's avatar
    Anonymous
    6 years ago

    I tried the 2nd suggestion, it worked. Thanks!

    To add a 3rd group_by category, just add it to the summarize table?

     

    averageX(summarize(Table, Table[ID], Table[DATE],Table[Type],"_1", max(Table[Data1])),[_1])

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Anonymous ,

Try like

Averagex(union(distinct(Table2[Value]),distinct(Table2[Value])),[Value])

 

Can you share sample data and sample output in table format?

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Sorry. See edited post above...I tried to clarify why I need distinct average...

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , Try as a new measure

     

    averageX(values(Table[Type]), max(Table[Data1]))

    or
    averageX(summarize(Table,Table[DATE],Table[Type],"_1", max(Table[Data1])),[_1])

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      I tried the 2nd suggestion, it worked. Thanks!

      To add a 3rd group_by category, just add it to the summarize table?

       

      averageX(summarize(Table, Table[ID], Table[DATE],Table[Type],"_1", max(Table[Data1])),[_1])