Forum Discussion

camilocorralesg's avatar
camilocorralesg
Advocate I
4 years ago
Solved

Problem with TOP N results

Hi everybody, I'm experiencing some trouble with my DAX measure to calculate dynamic TOP N values based on agent performance. First, my Income measure is = SUM(Income) Then, I calculated this DAX m...
  • Greg_Deckler's avatar
    Greg_Deckler
    4 years ago

    camilocorralesg Well, you aren't accounting for the Year context so your RANKX in 2020 is likely returning different top agents than across all years. So, if a particular agent did really well in 2020 and better than your top performers overall, this would account for the discrepency. You could solve this by doing something like:

    Measure =
  VAR __Table = SUMMARIZE(ALL('fct_hotel_revenue),[agent],"__Rank",[RANK INCOME])
  VAR __Agents = SELECTCOLUMNS(FILTER(__Table,[__Rank]<=6),"agent",[agent])
  VAR __Table1 = FILTER('fct_hotel_revenue',[agent] IN __Agents)
RETURN
  SUMX(__Table1,[Income])

    The idea here is to first get your top agents (__Agents) across all years or all whatever you have in your visual. You then filter your fact table within the context of the visual (this case years) so that you only return those agents in each of the years and then just do an X aggregation across that table.

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
4 years ago

camilocorralesg Well, you aren't accounting for the Year context so your RANKX in 2020 is likely returning different top agents than across all years. So, if a particular agent did really well in 2020 and better than your top performers overall, this would account for the discrepency. You could solve this by doing something like:

Measure =
  VAR __Table = SUMMARIZE(ALL('fct_hotel_revenue),[agent],"__Rank",[RANK INCOME])
  VAR __Agents = SELECTCOLUMNS(FILTER(__Table,[__Rank]<=6),"agent",[agent])
  VAR __Table1 = FILTER('fct_hotel_revenue',[agent] IN __Agents)
RETURN
  SUMX(__Table1,[Income])

The idea here is to first get your top agents (__Agents) across all years or all whatever you have in your visual. You then filter your fact table within the context of the visual (this case years) so that you only return those agents in each of the years and then just do an X aggregation across that table.

 

  • camilocorralesg's avatar
    camilocorralesg
    Advocate I
    4 years ago

    Greg_Deckler Hi Greg, thanks for your help. The problem with this virtual table is that I'm getting 1 for each agent, how should the Rankx function be applied in this case? 

    Tabla = SUMMARIZE(ALL(fct_hotel_revenue),[agent],"__Rank",RANKX(ALL(fct_hotel_revenue[agent]), [Income],,DESC), "_Income", [Income])
  • camilocorralesg's avatar
    camilocorralesg
    Advocate I
    4 years ago

    Greg_Deckler  Hey Greg, first I want to thank you I finally get the desired result, I had to do it this way: 

    changed the rank function to 

    RANKX(SUMMARIZE(ALL(fct_hotel_revenue), fct_hotel_revenue[agent], "Income", [Income] ), [Income],, DESC )
     
    then
    var agentss = SUMMARIZE(ALL(fct_hotel_revenue),[agent],"__Rank",[Rank], "Income", [Income])
    var agentss___ = SELECTCOLUMNS(FILTER(agentss, [__Rank] <= 6), "agent", [agent])
    var filterss = FILTER(fct_hotel_revenue, fct_hotel_revenue[agent] IN agentss___)
    RETURN
    CALCULATE([Income], filterss).
     
    Now, the problem I'm facing is that I need the rank to be dynamic, for that I created a parameter that goes from 3 to 20, so when the user chooses the value it only shows the data for that Top N, the thing is that when I try to put SELECTCOLUMNS(FILTER(agentss, [__Rank] <= SELECTEDVALUE(Ranking[Ranking]), "agent", [agent]) I get all blanks, can you help me understand why this happens?