Forum Discussion

Jkaelin's avatar
Jkaelin
Resolver I
9 years ago
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...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    9 years ago

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

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
9 years ago

That method of pasting data is perfect!

 

Based on TomMartens indicated, build your tables like this:

 

Table = TOPN(2,FILTER(Factors,Factors[Valuation]<=PERCENTILE.INC(Factors[Valuation],.5)),[Factor 2])
Greg_Deckler's avatar
Greg_Deckler
Community Champion
9 years ago

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