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

     

TomMartens's avatar
TomMartens
Super User
9 years ago

Wondering if this could be an idea
AVERAGEX(
UNION(

  TOPN(2,...,MEASURE1),
  TOPN(2,...,MEASURE2),
  TOPN(2,...,MEASURE3)
),
[RETURN]
)

To create sample data, maybe you export a table visual that contains the data, and upload the csv to OneDrive or DropBox  and share the link (please make sure that no unwanted aggregations have an effect of the data you want to export)

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

     

  • 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 

    • TomMartens's avatar
      TomMartens
      Super User
      9 years ago

      Hmm, 

       

      wondering what has to happen when

      • one PrimaryID satisfies more than one rule, meaning the same record is found in in the top 2 for factor 1 and factor 2
      • the 3 value is identical to the 2nd or even the Top 3 values are identical
      • Jkaelin's avatar
        Jkaelin
        Resolver I
        9 years ago

        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!