cancel
Showing results for
Did you mean:
Anonymous
Not applicable

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

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.

1 ACCEPTED SOLUTION
Community Support

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.

2 REPLIES 2
Community Support

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
Not applicable

Hi ,

can somebody help on this issue please??

Thanks.

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors