cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Resolver I
Resolver I

DAX Measure Help! (Multiple TopN criteria?)

Good morning,

 

This measure is critical to our model, but I'm afraid it's too complex for me.  

 

Goal:  I want the "Average" of the Return Column, for the Top 2 of Factors {1,2, & 3} within the Top Half of Valuation field. 

In total, 6 records will be averaged.  A sample table is below, to the right of the sample table is the results displayed manually.  

 

TopN of Factors.PNG

My attempts have been unsuccessful so far.  I've obtained the average return for the top half of the valuation field via this measure:  Measure 1:=CALCULATE(average(Table1[Return]),Filter(Table1,[Valuation]<=PERCENTILE.INC([Valuation],0.5)))

 

I've obtained the average return for the Top 2 of Factor 1, within the Top Half of Valuation field via this measure:

=AVERAGEX(TOPN(2,Table1,Table1[Factor 1]),[Measure1]).

 

When attempting to calculate the results for fields Factor 2 & 3, This method DID NOT work:

=AVERAGEX(TOPN(2,Table1,Table1[Factor 1]) && TOPN(2,Table1,Table1[Factor 2]),[Measure1]).

 

Any advice or tips on how to resolve this dilemma?  I need to rank multiple fields, within a field, and average them.

 

Thank you & kindly,

James

15 REPLIES 15

@TomMartens  If a PrimaryID satisfies more than one rule, the total records will be reduced.  So instead of 6 total records, their would be 5 (b/c 1 record met 2 criteria).  Ultimately, this measure is to backtest some stock portfolios of 30 stocks.  So the portfolio would have maybe 25-30 holdings depending on stocks that have overlapping criteria met.  Thank you for your help!

@Greg_Deckler @TomMartens

 

Maybe I'm mistaken, but this formula is returning a Semantic #Error, "The measure refers to Multiple Columns".  I think the [Factor 2] is causing it to error, but the logic seems to work.  What am I missing??

 

Table = TOPN(2,FILTER(Factors,Factors[Valuation]<=PERCENTILE.INC(Factors[Valuation],.5)),[Factor 2])

 

You need to create it as a table, not a measure.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@Greg_Deckler

 

Thanks for the tips.  I may just be in over my head here because I'm not sure what you mean by create it as a table vs. a measure.  I only have done calculated columns & measures.  Sorry for my ignorance.  

@Greg_Deckler@TomMartens

 

I believe I got the measure to get me the correct value.  I think it works now!  Thank you guys so much for your help.  

 

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

 

Yep, that should definitely work as a single measure.

 

For future reference you can create a calculated table by going to the Modeling tab and clicking "New Table". The difference is that a table expects to return 1 or more rows versus a measure which expects a single value to be generated.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors