Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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 |
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])
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.
I believe I got the measure to get me the correct value. I think it works now! Thank you guys so much for your help.
Yep, that should definitely work as a single measure.
For future reference you can create a calculated table by going to the Modeling tab and clicking "New Table". The difference is that a table expects to return 1 or more rows versus a measure which expects a single value to be generated.
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 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
Hmm,
wondering what has to happen when
@TomMartens If a PrimaryID satisfies more than one rule, the total records will be reduced. So instead of 6 total records, their would be 5 (b/c 1 record met 2 criteria). Ultimately, this measure is to backtest some stock portfolios of 30 stocks. So the portfolio would have maybe 25-30 holdings depending on stocks that have overlapping criteria met. Thank you for your help!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |