Forum Discussion
Help with a Top value
Hi Guys,
I am trying to create a "Hall of Fame" tab in my report.
pretty much, i want the user to select one of many measures that describe my data, and then be able to select top 10% of values, top 25% of values, and top 50% of values.
a sample of my data is below:
| Name | Date | Value |
| a | 1/1/2017 | 91 |
| a | 1/2/2017 | 23 |
| a | 1/3/2017 | 64 |
| a | 1/4/2017 | 74 |
| a | 1/5/2017 | 47 |
| a | 1/6/2017 | 86 |
| b | 1/1/2017 | 69 |
| b | 1/2/2017 | 92 |
| b | 1/3/2017 | 74 |
| b | 1/4/2017 | 66 |
| b | 1/5/2017 | 28 |
| b | 1/6/2017 | 38 |
| c | 1/1/2017 | 35 |
| c | 1/2/2017 | 48 |
| c | 1/3/2017 | 92 |
| c | 1/4/2017 | 68 |
| c | 1/5/2017 | 81 |
| c | 1/6/2017 | 30 |
i am looking to make some measures/filters that would select:
top 10% of "names" with:
the highest max value,
the highest cumulative value
the highest average value for the first 3 months.
then the selection would plot the names on a graph. the above data is a quick representation of my data (where my data set has ~8Million rows)
Hi aar0n,
Create a new table Options like below:
Then create a measure in the table using DAX:
Result = SWITCH ( TRUE (), SELECTEDVALUE ( Options[Options] ) = "Top 10% Names By Average", [Top 10% Names By Average], SELECTEDVALUE ( Options[Options] ) = "Top 10% Names By Total", [Top 10% Names By Total], SELECTEDVALUE ( Options[Options] ) = "Top 10% Names By Max", [Top 10% Names By Max] )Regards,
Jimmy Tao
4 Replies
Hi aar0n,
To achieve your requirement, you should create rankings by max value, total value and average value. Create three calculate columns using DAX below:
Rank By Max = RANKX(ALL(Table1), CALCULATE(MAX(Table1[Value]), ALLEXCEPT(Table1, Table1[Name])),,DESC,Dense) Rank By Total = RANKX(ALL(Table1), CALCULATE(SUM(Table1[Value]), ALLEXCEPT(Table1, Table1[Name])),,DESC,Dense) Rank By Average = RANKX(ALL(Table1), CALCULATE(AVERAGE(Table1[Value]), ALLEXCEPT(Table1, Table1[Name])),,DESC,Dense)
Then to achieve the names which achieve the top 10% rankings above, create three measures using DAX below:
Top 10% Names By Max = VAR divide_ = ROUNDUP(MAX(Table1[Rank By Max]) * 0.1, 0) VAR name_ = CALCULATETABLE(VALUES(Table1[Name]), FILTER(ALL(Table1), Table1[Rank By Max] <= divide_)) RETURN CONCATENATEX(name_, Table1[Name], ",") Top 10% Names By Total = VAR divide_ = ROUNDUP(MAX(Table1[Rank By Total]) * 0.1, 0) VAR name_ = CALCULATETABLE(VALUES(Table1[Name]), FILTER(ALL(Table1), Table1[Rank By Total] <= divide_)) RETURN CONCATENATEX(name_, Table1[Name], ",") Top 10% Names By Average = VAR divide_ = ROUNDUP(MAX(Table1[Rank By Average]) * 0.1, 0) VAR name_ = CALCULATETABLE(VALUES(Table1[Name]), FILTER(ALL(Table1), Table1[Rank By Average] <= divide_)) RETURN CONCATENATEX(name_, Table1[Name], ",")
The result is like this:
Hope it's helpful to you.
Jimmy Tao
hi v-yuta-msft,
thank you for your quick response. this reply is very helpful, but there are some changes i was hoping for,
for example,
instead of just displaying the measure in a data card, would it be possible to use the measure (or a different method) in some type of filter?
what i was hoping for was the ability to click "top 10% names by max" and then my other visuals in my report would filter to those names.The reason i need this, is that Power bi only graphs a subset of my data (and i can't truly see the top percentile of data)
Hi aar0n,
Create a new table Options like below:
Then create a measure in the table using DAX:
Result = SWITCH ( TRUE (), SELECTEDVALUE ( Options[Options] ) = "Top 10% Names By Average", [Top 10% Names By Average], SELECTEDVALUE ( Options[Options] ) = "Top 10% Names By Total", [Top 10% Names By Total], SELECTEDVALUE ( Options[Options] ) = "Top 10% Names By Max", [Top 10% Names By Max] )Regards,
Jimmy Tao
