topic Re: Distinct average by two categories in DAX Commands and Tips

Distinct average by two categories

Anonymous — Fri, 04 Sep 2020 16:20:46 GMT

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 Type Data1 Data2 Data3...etc.

1 01/01/2020 A 3 8

1 01/01/2020 B 5 2

1 01/01/2020 B 5 4

1 01/01/2020 B 5 3

1 01/01/2020 C 7 1

1 01/01/2020 C 7 2

2 01/01/2020 C 7 2

2 01/01/2020 C 7 1

etc...

I would like the Distinct AVG_ID_DATE measure to give: average for Data 1 (ID/Date/Type):

ID DATE AVG_ID_DATE

1 01/01/2020 5 (3+5+7)/3

Thanks!

Re: Distinct average by two categories

Greg_Deckler — Fri, 04 Sep 2020 15:41:09 GMT

Anonymous Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: Distinct average by two categories

amitchandak — Fri, 04 Sep 2020 15:45:22 GMT

Anonymous ,

Try like

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

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

Re: Distinct average by two categories

Anonymous — Fri, 04 Sep 2020 16:21:27 GMT

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

Re: Distinct average by two categories

amitchandak — Fri, 04 Sep 2020 17:03:32 GMT

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])

Re: Distinct average by two categories

CNENFRNL — Fri, 04 Sep 2020 19:27:53 GMT

Anonymous I suggest transforming such a 2-dimensional into a 1-dimensional table in the first place, so that a versatile measure can be authored instead of specific Data1, Data2, ...

Measure = AVERAGEX ( DISTINCT ( DS ), DS[Value] )

Re: Distinct average by two categories

Anonymous — Fri, 04 Sep 2020 20:03:10 GMT

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])

Re: Distinct average by two categories

Anonymous — Fri, 04 Sep 2020 20:04:45 GMT

That probably would simplify things (a simpler table). Thanks!