Help with a Top value

[aar0n]

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)

[v-yuta-msft]

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

[aar0n]

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)

[v-yuta-msft · Accepted 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

[aar0n]

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