cancel
Showing results for
Did you mean:
Resolver I

## Re-Write DAX Measure to NOT calculate Average of Averages

Good morning,

I have a DAX measure that has been very useful.  However, I discovered it was calculating the "average of averages" instead of the average of records.

Below, the measure is capturing the Top 2 of three different factors.  Instead of averaging the 6 records together, it is calculating the average of the average of the 3x Top 2's.

DAX Test Measure :=
CALCULATE (
AVERAGEX (
UNION (
TOPN ( 2, Table1, Table1[Factor 1] ),
TOPN ( 2, Table1, Table1[Factor 2] ),
TOPN ( 2, Table1, Table1[Factor 3] )
),
[Return]
),
FILTER ( Table1, [Valuation] <= PERCENTILE.INC ( [Valuation], 0.5 ) )
)

Is their a method to re-write the above DAX measure so it calculates the average of the records instead of the 'avg. of averages'?

Thank you,

James K

1 ACCEPTED SOLUTION

Accepted Solutions
Community Champion

## Re: Re-Write DAX Measure to NOT calculate Average of Averages

@Jkaelin

```Correct Answer=
AVERAGEX (
DISTINCT (
UNION (
SELECTCOLUMNS (
TOPN (
2,
FILTER (
Factors,
Factors[Valuation] <= PERCENTILE.INC ( Factors[Valuation], 0,5 )
),
Factors[Factor 1]
),
"ID", Factors[PrimaryID],
"Return", Factors[Return]
),
SELECTCOLUMNS (
TOPN (
2,
FILTER (
Factors,
Factors[Valuation] <= PERCENTILE.INC ( Factors[Valuation], 0,5 )
),
Factors[Factor 2]
),
"ID", Factors[PrimaryID],
"Return", Factors[Return]
),
SELECTCOLUMNS (
TOPN (
2,
FILTER (
Factors,
Factors[Valuation] <= PERCENTILE.INC ( Factors[Valuation], 0,5 )
),
Factors[Factor 3]
),
"ID", Factors[PrimaryID],
"Return", Factors[Return]
)
)
),
[Return]
)```

Regards

Victor

Lima - Peru

Lima - Peru
5 REPLIES 5
Super User I

## Re: Re-Write DAX Measure to NOT calculate Average of Averages

Is [Return] an average measure?

Proud to be a Datanaut!
Resolver I

## Re: Re-Write DAX Measure to NOT calculate Average of Averages

@KHorseman

Yes, [Return] is the column that will be averaged at a record level.  It's technically records of stocks that will be averaged for a portfolio.

Highlighted
Resolver I

## Re: Re-Write DAX Measure to NOT calculate Average of Averages

More information:  Below is a link to my sample file and a visual of what I'm trying to accomplish with the measure.

https://www.dropbox.com/s/ycotbvtcpipg7rk/DAX%20Oppty%20Measure.xlsx?dl=0

Community Champion

## Re: Re-Write DAX Measure to NOT calculate Average of Averages

@Jkaelin

```Correct Answer=
AVERAGEX (
DISTINCT (
UNION (
SELECTCOLUMNS (
TOPN (
2,
FILTER (
Factors,
Factors[Valuation] <= PERCENTILE.INC ( Factors[Valuation], 0,5 )
),
Factors[Factor 1]
),
"ID", Factors[PrimaryID],
"Return", Factors[Return]
),
SELECTCOLUMNS (
TOPN (
2,
FILTER (
Factors,
Factors[Valuation] <= PERCENTILE.INC ( Factors[Valuation], 0,5 )
),
Factors[Factor 2]
),
"ID", Factors[PrimaryID],
"Return", Factors[Return]
),
SELECTCOLUMNS (
TOPN (
2,
FILTER (
Factors,
Factors[Valuation] <= PERCENTILE.INC ( Factors[Valuation], 0,5 )
),
Factors[Factor 3]
),
"ID", Factors[PrimaryID],
"Return", Factors[Return]
)
)
),
[Return]
)```

Regards

Victor

Lima - Peru

Lima - Peru
Resolver I

## Re: Re-Write DAX Measure to NOT calculate Average of Averages

@Vvelarde

Insane solution!  I have been working on this all morning with several variations of Calculate/ TopN/ RankX/ Multiple Filter/ etc.  This is awesome help & I really appreciate it.  You the man!  I don't fully understand how it works, but I'll study it to death & see if I can't make some sense of it.

Thank you,

James

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

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

Top Solution Authors
Top Kudoed Authors