cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

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

 

 Capture.PNG

 

10 REPLIES 10
Highlighted

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.

Highlighted
Helper IV
Helper IV

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] )
)
Highlighted
Helper IV
Helper IV

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! Smiley Happy

Highlighted

Re: Distinct count filtered Average

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

Highlighted
Helper II
Helper II

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

Highlighted
Helper II
Helper II

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 Smiley Frustrated

 

Highlighted
Helper II
Helper II

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 

 

Highlighted
Community Support
Community Support

Re: Distinct count filtered Average

Hi @abehrmann

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

I don't know why you need to create relationships, could you tell more about this?

 

Additionally, please have a look at my analysis about your case->

With rafaelmpsantos's measure, you could create the desired table on a report by table visual

3.png

 

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

2.png

 

Then add columns in the table visual, and right-click on the "days in ops" column in the Value field, select "average" 

4.png

 

 

Best Reagrds

Maggie

Highlighted
Helper II
Helper II

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 

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors