Forum Discussion

Jkaelin's avatar
Jkaelin
Icon for Resolver I rankResolver 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
Icon for Community Champion rankCommunity Champion
9 years ago

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

Jkaelin's avatar
Jkaelin
Icon for Resolver I rankResolver I
9 years ago

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.