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