Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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)
Solved! Go to Solution.
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
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
hi @v-yuta-msft
this reply is so close, but not exactly what i am looking for..
in the end, i am hoping to have multiple visuals, and not just a data card.
in my example screenshot below, if i click on "Top 10% Names By Total", i need the graph visual to only show me the name "A"
if i click on "top 10% Names by Max", i need the graph visual to show b,c
in my small subset of test data, it may not be too difficult to filter from the data card value.. but my actual dataset has ~8 million rows, and ~5k names...