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])
Wondering if you could break the problem down in a table with distinct primary ID's. Essentially, create a column/measure for each of the items you have displayed. You should be able to use RANKX for most of them. Could you post your sample data in a form that is easy to copy and paste into an Enter Data query? That would save a huge amount of time.
If you did it this way, you could possibly use filters to get what you wanted.
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
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.
