Forum Discussion
Distinct count filtered Average
I fiddled around with your sample data, and this is the only solution that I came up with:
First create an index column with a distinct value for each row.
Then use this code for your measure:
Ops Average Age =
CALCULATE (
AVERAGEX ( OfficeTrax, OfficeTrax[Days in Ops] ),
ALL ( OfficeTrax[Index] )
)as an alternative i have duplicated the table in the query editor and repoved duplicates and then calculated the average that way. the number is correct but i am having trouble with the relationships on the page level filters :smileyfrustrated:
Hi abehrmann
i am having trouble with the relationships on the page level filters
I don't know why you need to create relationships, could you tell more about this?
Additionally, please have a look at my analysis about your case->
With rafaelmpsantos's measure, you could create the desired table on a report by table visual
If you want to create a new table as your desired table, addition to create one in query editor, you could create a new table with DAX
Table = SUMMARIZE(Sheet1,Sheet1[request],Sheet1[days in ops])
Then add columns in the table visual, and right-click on the "days in ops" column in the Value field, select "average"
Best Reagrds
Maggie
v-juanli-msft the new table worked for me to get accurate averages. But now when i filter the rest of the data these averages do not interact with the rest of the page
as an alternative i have duplicated the table in the query editor and repoved duplicates and then calculated the average that way. the number is correct but i am having trouble with the relationships on the page level filters
