cancel
Showing results for
Did you mean:
Regular Visitor

## Distinct count filtered Average

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 #]))

10 REPLIES 10
Highlighted
Established Member

## Re: Distinct count filtered Average

try it

measure = AVERAGEX(DISTINCT(OfficeTrax);OfficeTrax[Days in OPS])

If it solve, please mark as solved and give your positive feedback.

Member

## Re: Distinct count filtered Average

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] )
)```
Member

## Re: Distinct count filtered Average

@rafaelmpsantos

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!

Established Member

## Re: Distinct count filtered Average

Thanks @Christann, i have big difficulties with english, i'm brazilian, but i try to help.

Regular Visitor

## Re: Distinct count filtered Average

@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

Regular Visitor

## Re: Distinct count filtered Average

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

Regular Visitor

## Re: Distinct count filtered Average

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

Community Support Team

## Re: Distinct count filtered Average

i am having trouble with the relationships on the page level filters

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

Regular Visitor

## Re: Distinct count filtered Average

@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