Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
camilocorralesg
Advocate I
Advocate I

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 measure to get the rank: 

RANK INCOME = RANKX(ALL(fct_hotel_revenue[agent]), [Income], ,DESC), after I set my rank value to 6 I get this:
camilocorralesg_0-1662328784912.png

 

But the idea is I can display the total the revenue made by those TOP N agents on different charts, for that purpose I created the following measure: 

TOP N revenue = CALCULATE([Income], TOPN(SELECTEDVALUE(Ranking[Ranking]), ALL(fct_hotel_revenue[agent]), [Income], DESC))
the thing is that when I put that measure in a line chart that contains year, I get the wrong values but the right total.
This should be the output by year:
camilocorralesg_1-1662329025829.png

Instead, Im getting this:

camilocorralesg_2-1662329050628.png

I also tried to calculate the top N with in this other way but I'm getting the same poor results, as you can see the values don't match the total, but the total matches the right value.

Other way = CALCULATE([Income], TOPN(6, ALL(fct_hotel_revenue[agent]), [Income], DESC), VALUES(fct_hotel_revenue[agent]))
Why is this happening? Do I need to change the context for every chart?
 
1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@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.

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

@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.

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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?

@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? 

camilocorralesg_0-1662388153919.png

Tabla = SUMMARIZE(ALL(fct_hotel_revenue),[agent],"__Rank",RANKX(ALL(fct_hotel_revenue[agent]), [Income],,DESC), "_Income", [Income])

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors