Forum Discussion
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 Top Half of Valuation field.
In total, 6 records will be averaged. A sample table is below, to the right of the sample table is the results displayed manually.
My attempts have been unsuccessful so far. I've obtained the average return for the top half of the valuation field via this measure: Measure 1:=CALCULATE(average(Table1[Return]),Filter(Table1,[Valuation]<=PERCENTILE.INC([Valuation],0.5)))
I've obtained the average return for the Top 2 of Factor 1, within the Top Half of Valuation field via this measure:
=AVERAGEX(TOPN(2,Table1,Table1[Factor 1]),[Measure1]).
When attempting to calculate the results for fields Factor 2 & 3, This method DID NOT work:
=AVERAGEX(TOPN(2,Table1,Table1[Factor 1]) && TOPN(2,Table1,Table1[Factor 2]),[Measure1]).
Any advice or tips on how to resolve this dilemma? I need to rank multiple fields, within a field, and average them.
Thank you & kindly,
James
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])
15 Replies
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)
