cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jkaelin Resolver I
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
Vvelarde Community Champion
Community Champion

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

@Jkaelin

 

Hi, please try with this:

 

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

View solution in original post

5 REPLIES 5
Super User I
Super User I

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

Is [Return] an average measure?


Did I answer your question? Please mark it as a solution.

Proud to be a Datanaut!
Jkaelin Resolver I
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
Jkaelin Resolver I
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

 

Power BI Dax Measure.PNG

 

 

 

 

Vvelarde Community Champion
Community Champion

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

@Jkaelin

 

Hi, please try with this:

 

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

View solution in original post

Jkaelin Resolver I
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

Helpful resources

Announcements
New Ranks Launched March 24th!

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!

‘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

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 Microsoft Power Platform event series.

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

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