Forum Discussion
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 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!
7 Replies
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.Anonymous ,
Try like
Averagex(union(distinct(Table2[Value]),distinct(Table2[Value])),[Value])
Can you share sample data and sample output in table format?
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 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] )
