Forum Discussion
DAX Measure Help! (Multiple TopN criteria?)
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 Thanks for the quick response. Not sure how to post sample data in a form that is designed for Enter Data query? I would love to know & I will do it. Below is a simple copy & paste from Excel. I have considered RankX, but wasn't sure how to execute that for multiple fields.
| PrimaryID | Return | Valuation | Factor 1 | Factor 2 | Factor 3 |
| 2 | -79% | 0.01 | 83.2 | 8.2 | 87.3 |
| 13 | -4% | 0.04 | 72.8 | 63.6 | 48.1 |
| 4 | -38% | 0.07 | 25.7 | 40.2 | 36.3 |
| 28 | -30% | 0.07 | 55.4 | 6.0 | 37.8 |
| 16 | -72% | 0.07 | 92.9 | 46.7 | 20.2 |
| 7 | -27% | 0.08 | 5.0 | 6.4 | 78.5 |
| 17 | -93% | 0.08 | 85.0 | 38.6 | 50.0 |
| 25 | -3% | 0.16 | 11.2 | 98.9 | 45.0 |
| 9 | -100% | 0.18 | 30.2 | 81.0 | 64.5 |
| 23 | -16% | 0.20 | 78.3 | 86.6 | 28.7 |
| 27 | -78% | 0.21 | 96.9 | 60.9 | 13.2 |
| 14 | -69% | 0.24 | 9.3 | 48.5 | 47.5 |
| 24 | -51% | 0.25 | 96.7 | 60.8 | 59.7 |
| 10 | -52% | 0.32 | 51.1 | 78.9 | 79.9 |
| 21 | -40% | 0.35 | 72.6 | 82.3 | 99.7 |
| 22 | -25% | 0.36 | 59.5 | 92.3 | 13.3 |
| 12 | -9% | 0.50 | 58.0 | 44.0 | 63.7 |
| 26 | -55% | 0.50 | 36.9 | 18.7 | 52.8 |
| 29 | -22% | 0.52 | 54.1 | 40.5 | 80.1 |
| 18 | -85% | 0.57 | 55.0 | 51.7 | 70.9 |
| 30 | -42% | 0.61 | 10.4 | 89.5 | 45.0 |
| 19 | -44% | 0.63 | 16.1 | 47.0 | 13.3 |
| 15 | -1% | 0.63 | 17.7 | 81.7 | 74.2 |
| 8 | -36% | 0.70 | 57.6 | 89.8 | 28.8 |
| 11 | -96% | 0.81 | 64.6 | 31.0 | 67.4 |
| 20 | -74% | 0.85 | 18.6 | 72.0 | 28.6 |
| 3 | -78% | 0.85 | 67.6 | 48.9 | 60.3 |
| 1 | -33% | 0.87 | 0.3 | 92.3 | 98.1 |
| 5 | -98% | 0.93 | 19.5 | 22.0 | 31.0 |
| 6 | -92% | 0.95 | 22.2 | 59.1 | 54.2 |
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)
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
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
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
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