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

     

Jkaelin's avatar
Jkaelin
Resolver I
9 years ago

TomMartens

 

Thanks for your help!  The measure you provided gives me the return for the 3 factors, but did not account for the factors within the Top Half of the Valuation field.  Is it possible to include that within the measure?  The measure you did provide is very helpful.

 

This is the file on Dropbox (I think I did it correctly):  https://www.dropbox.com/s/ycotbvtcpipg7rk/DAX%20Oppty%20Measure.xlsx?dl=0

TomMartens's avatar
TomMartens
Super User
9 years ago

Hey,

 

the basic idea is to union 3 tables,

as far as i understod, you already have found the proper value for factor 1.

maybe you have to use 3 filter statements, that each return a table, even if this table just contains one column.

 

Maybe you find a way to share some data