Forum Discussion
Distinct count filtered Average
I am looking to get an average of Days in ops based on distinct Request # I tried : Ops Average Age = divide(sum(OfficeTrax[Days in OPS]),distinctcount(OfficeTrax[Request #])) But it doesnt ...
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