Get the average of a column value based on total count of values in ID column

[Anonymous]

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:

• The count of 111 runid is '10'. so for all those durations with runid 111 should be divided by the 10.
• The count of 222 runid is '6'. so for all those durations with runid 222 should be divided by the 6.
• The count of 333 runid is '8'. so for all those durations with runid 333 should be divided by the 8.

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.

[v-chenwuz-msft · Accepted 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.

[Anonymous]

Hi ,

can somebody help on this issue please??

Thanks.