Forum Discussion
Help with a Top value
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...Example