Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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:
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:
Instead, Im getting this:
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.
Solved! Go to Solution.
@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 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 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
@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?
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
31 | |
15 | |
11 | |
10 | |
8 |