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 |
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])
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])Maybe I'm mistaken, but this formula is returning a Semantic #Error, "The measure refers to Multiple Columns". I think the [Factor 2] is causing it to error, but the logic seems to work. What am I missing??
Table = TOPN(2,FILTER(Factors,Factors[Valuation]<=PERCENTILE.INC(Factors[Valuation],.5)),[Factor 2])
You need to create it as a table, not a measure.
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.