Dynamically filter graph depending on Rank

[wwolfg]

Hi,

I want to create 4 time series graph that represent the rank 1, rank 2, rank 3 and rank 4. 

I tried several ways with no success.

First I make a measure for the scrap quantity produced: 

Merma Kg = SUM(Merma[Quantity])

ThenI make the ranking, which works apparently fine in a table:

Rank Procesos Merma =RANKX(ALLSELECTED(Merma[Proceso]),CALCULATE([Merma Kg]))

Then I made 1 measure for each rank For example for the rank 1 and 2:

Merma Rank 1 = if([Rank Procesos Merma]=1,[Merma Kg],BLANK())

Merma Rank 2= if([Rank Procesos Merma]=2,[Merma Kg],BLANK())

But it does not work at all. I watched this video and made some changes and they work well in a table but not in the graph.

Merma Top 1 =

if(ISFILTERED(Merma[Proceso]),if([Rank Procesos Merma]=1,[Merma Kg],BLANK()),CALCULATE([Merma Kg],TOPN(1,VALUES(Merma[Proceso]),[Merma Kg]))) This does work but it is because I am using top 1, but it does not work when I use rank 2, etc.

Here is the link for some sample data and this is a summarized table of the data, and how I want to visualize it

Proceso	Merma Kg	Rank Procesos Merma
Proceso 5	20963	1
Proceso 4	18781	2
Proceso 10	17537	3
Proceso 3	13558	4
Proceso 2	10493	5
Proceso 1	9671	6
Proceso 7	9342	7
Proceso 9	8122	8
Proceso 6	2750	9
Proceso 8	1272	10

I want to be able to dinamycally calculate the ranks and visualizations when I change the date.

I appreciate your help beforehand.

Walter

[lbendlin · Accepted solution]

Your rank will be calculated on different filter contexts.  Process 5 is only #1 overall, but not for the individual rows.  So you would have to do two steps 

- find the process that is ranked #1 overall

- show the items for that process.

Merma Rank 1 = 
var a = summarize(ALLSELECTED(Merma),[Proceso],"sm",[Merma Kg])
var b = maxx(TOPN(1,a,[sm]),[Proceso])
return calculate([Merma Kg],Merma[Proceso]=b)

For Rank 2 you need to do some extra gymnastics

Merma Rank 2 = 
var a = summarize(ALLSELECTED(Merma),[Proceso],"sm",[Merma Kg])
var b = maxx(TOPN(1,TOPN(2,a,[sm]),[sm],ASC),[Proceso])
return calculate([Merma Kg],Merma[Proceso]=b)

[wwolfg]

Thank you, it worked perfect. I appreaciate very much your help.