Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello, I have been working to build a calculated measure that will restart a rank set based on the relative hierarchy of my company's locations. It will rank the Divisions separately from the Regions, it will rank the Regions separately from the Districts, and it will rank the Districts separately from the individual Locations.
I have accomplished this by using the following DAX:
CY_WTD_Net_Revenue_Comp_YoY_Variance_Rank = IF( ISFILTERED(dim_Location[LocationID]), RANKX(ALL(dim_Location[LocationID]),CALCULATE(SUM(Revenue_Table[CY_WTD_Net_Revenue_Comp_YoY_Variance])),,DESC,Dense), IF( ISFILTERED(dim_Location[DistrictName]), RANKX(ALL(dim_Location[DistrictName]),CALCULATE(SUM(Revenue_Table[CY_WTD_Net_Revenue_Comp_YoY_Variance])),,DESC,Dense), IF( ISFILTERED(dim_Location[RegionName]), RANKX(ALL(dim_Location[RegionName]),CALCULATE(SUM(Revenue_Table[CY_WTD_Net_Revenue_Comp_YoY_Variance])),,DESC,Dense), IF( ISFILTERED(dim_Location[DivisionName]), RANKX(ALL(dim_Location[DivisionName]),CALCULATE(SUM(Revenue_Table[CY_WTD_Net_Revenue_Comp_YoY_Variance])),,DESC,Dense) ) ) ) )
This calculated measure works like a charm when used to SUM most columns. However, with this particular Year over Year variance column, it skips a number. For example:
Location -- Year over Year Variance -- Rank
UniqueLocationID #1 -- $199,863 -- 1
UniqueLocationID #2 -- $129,432 -- 2
UniqueLocationID #3 -- $99,580 -- 3
UniqueLocationID #4 -- $98,081 -- 4
UniqueLocationID #5 -- $91,458 -- 5
UniqueLocationID #6 -- $35,106 -- 6
UniqueLocationID #7 -- ($22,488) -- 8
UniqueLocationID #8 -- ($61,628) -- 9
UniqueLocationID #9 -- ($249,187) -- 10
As you can see, the rank skips the number 7. No other locations, other than the 9 unique ones listed, exist in the given district. I have replicated the ranking at the data layer as well with SQL and it ranks just fine, so it is not an underlying data issue. Furthermore, this issue only happens with certain districts, not all districts.
Does anyone know why/how this could be occurring? Is it the ISFILTERED() function?
As a side note, I do not think it has anything to do with the ALL() function. I have tried ALLNOBLANKROW() and ALLSELECTED() as well, and neither have affected the outcome.
Solved! Go to Solution.
This thread was initially marked as spam, but has recently been unflagged. In order to dodge waiting a week for a moderator reply, I posted this issue earlier this week in a separate thread located here:
https://community.powerbi.com/t5/Desktop/RANKX-Randomly-Skipping-Numbers/m-p/659878#M316885
The solution found is in that thread as well.
This thread was initially marked as spam, but has recently been unflagged. In order to dodge waiting a week for a moderator reply, I posted this issue earlier this week in a separate thread located here:
https://community.powerbi.com/t5/Desktop/RANKX-Randomly-Skipping-Numbers/m-p/659878#M316885
The solution found is in that thread as well.
Hi @Chase ,
Please share some sample data to help us clarify your data structure, it is hard to troubleshooting without any sample data to test.
How to Get Your Question Answered Quick
Regards,
Xiaoxin Sheng
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |