Forum Discussion

wwolfg's avatar
wwolfg
Frequent Visitor
2 years ago
Solved

Dynamically filter graph depending on Rank

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
 
ProcesoMerma KgRank Procesos Merma
Proceso 5209631
Proceso 4187812
Proceso 10175373
Proceso 3135584
Proceso 2104935
Proceso 196716
Proceso 793427
Proceso 981228
Proceso 627509
Proceso 8127210
 

 


 

I want to be able to dinamycally calculate the ranks and visualizations when I change the date.
 
I appreciate your help beforehand.
 
Walter
 
 
Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    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)

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    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's avatar
      wwolfg
      Frequent Visitor
      2 years ago

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