Distinct average by two categories

[crawfe]

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!

[CNENFRNL]

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

[crawfe]

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

[amitchandak]

@crawfe ,

Try like

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

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

[crawfe]

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

[amitchandak]

@crawfe , 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])

[crawfe · Accepted solution]

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

[Greg_Deckler]

@crawfe 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.