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

Anonymous — Tue, 14 Jun 2022 14:28:29 GMT

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.

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

Anonymous — Thu, 16 Jun 2022 08:16:22 GMT

Hi ,

can somebody help on this issue please??

Thanks.

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

v-chenwuz-msft — Fri, 17 Jun 2022 03:16:42 GMT

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.

topic Re: Get the average of a column value based on total count of values in ID column in DAX Commands and Tips

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

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

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