Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 get me the result i want.
Any ideas?
Ops Average Age = divide(sum(OfficeTrax[Days in OPS]),distinctcount(OfficeTrax[Request #]))
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
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
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
Hi @abehrmann
I guess the original table has some relationship with other tables, you want your new table interact with with other tables too.
Please show me some example of other tables and what you want to show in this new table when filter in another table.
Best Regards
Maggie
try it
measure = AVERAGEX(DISTINCT(OfficeTrax);OfficeTrax[Days in OPS])
If it solve, please mark as solved and give your positive feedback.
@rafaelmpsantos that measure gave me the same result as if i just averaged the column [days in ops]
below is a samlpe of what my goal is
Desired Result Sample Dataset
Request # Days in Ops Request # Days in Ops
R123 1 R123 1
R124 2 R124 2
R125 4 R125 4
R126 7 R123 1
AVG: 3.5 R126 7
R124 2
AVG: 2.833333333
I have been working on several replies only to find that you had already answered them. Great work!
P.S. Your solutions are more elegant than mine anyway!
Thanks @Christann, i have big difficulties with english, i'm brazilian, but i try to help.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |