Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi,
In a table i have column called "Object" which runs multiple times in a day and that runtime is captured in "StartTime" & "EndTime" columns, and the difference between the start and end time is captured in the "Duration" Column.
Each object will be having multiple runid's which is captured in "RunID" column which is a unique value.
For eg: Object AAA has two runid's called "111,222" and Object BBB has one runid called "333".
Now i would like to get the average of each duration by count of that particular runid which is captured in "New Duration" column.
For eg:
Sample screenshot for above query:
So since my Duration is "10" and total count of runid (111) is 10. So 10/10=1 that is my newduration.Similarly for all durations with runid's 111 should be divided bt 10.
How to achieve this using DAX query??
I am connecting to this table in powerbi via SQL direct query mode.
Thanks.
Solved! Go to Solution.
Hi @Anonymous ,
Maybe you can try this DAX:
Measure =
DIVIDE (
SUM ( 'Table'[Duration] ),
CALCULATE (
COUNTROWS ( 'Table' ),
ALLEXCEPT ( 'Table', 'Table'[RunID] )
)
)
Result:
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Maybe you can try this DAX:
Measure =
DIVIDE (
SUM ( 'Table'[Duration] ),
CALCULATE (
COUNTROWS ( 'Table' ),
ALLEXCEPT ( 'Table', 'Table'[RunID] )
)
)
Result:
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi ,
can somebody help on this issue please??
Thanks.
User | Count |
---|---|
20 | |
20 | |
14 | |
10 | |
8 |
User | Count |
---|---|
29 | |
28 | |
13 | |
12 | |
11 |