Forum Discussion
Solved
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...
Here is a full solution, you could consolidate this to a single table/measure:
Table 1 = TOPN(2,FILTER(Factors,Factors[Valuation]<=PERCENTILE.INC(Factors[Valuation],.5)),[Factor 1]) Table 2 = TOPN(2,FILTER(Factors,Factors[Valuation]<=PERCENTILE.INC(Factors[Valuation],.5)),[Factor 2]) Table 3 = TOPN(2,FILTER(Factors,Factors[Valuation]<=PERCENTILE.INC(Factors[Valuation],.5)),[Factor 3]) Table 4 = UNION('Table 1', 'Table 2', 'Table 3') Measure = AVERAGE('Table 4'[Return])
You need to create it as a table, not a measure.
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.
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.