cancel
Showing results for
Did you mean:
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.

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

@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!

Resolver I

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

Super User IV

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

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

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

Proud to be a Super User!

Resolver I

@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.

Resolver I

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

Super User IV

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.

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

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

Proud to be a Super User!

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform October Community Highlights

Check out the top community contributors across all of the communities

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