Forum Discussion
Rank X issues
Hi,
Thanks for the solutions Bibiano_Geraldo and DataNinja777 offered, and i want to offer some moreinformation for user to refer to.
hello stevejames , you can try the following.
Cost per head rank = RANKX( FILTER( ALLSELECTED('Costs'), [AreaName] in values( 'Costs'[AreaName]) 'Costs'[Total]<>0 && NOT(ISBLANK('Costs'[Total])) ), CALCULATE([Cost per head]), , DESC, DENSE )If the information cannot help you can you provide some sample data?
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi stevejames ,
It looks like the issue arises from how RANKX and CONCATENATEX operate in different evaluation contexts. RANKX ranks values based on a specified expression and operates in a row context, while CONCATENATEX simply aggregates values into a single string without changing the row context. Because of this, CONCATENATEX might be displaying the expected ordering, but RANKX may be ranking against a different set of values due to filter context.
One possible issue is that ALLSELECTED('Area'[Name]) might not be providing the correct filter context for ranking within categories. Since you are displaying all categories for a single area, ALLSELECTED('Area'[Name]) may not be the ideal table for ranking. Instead, you might need to adjust it to ALLSELECTED('Category'[Name]), ensuring the ranking takes place within the selected area but differentiates properly among categories.
Cost per head rank =
RANKX(
FILTER(
ALLSELECTED('Category'[Name]),
'Costs'[Total]<>0 && NOT(ISBLANK('Costs'[Total]))
),
CALCULATE([Cost per head]),
,
DESC,
DENSE
)
Using CONCATENATEX as a debugging tool can help visualize what RANKX is evaluating. Since CONCATENATEX does not modify row context, it can display the cost per head values and their corresponding rank in the same expression, allowing you to compare the expected order versus the calculated ranking.
Cost per head rank (concat debug) =
CONCATENATEX(
FILTER(
ALLSELECTED('Category'[Name]),
'Costs'[Total]<>0 && 'Costs'[Total]<>BLANK()
),
[Cost per head] & " | " & RANKX(
FILTER(
ALLSELECTED('Category'[Name]),
'Costs'[Total] <> 0 && NOT(ISBLANK('Costs'[Total]))
),
CALCULATE([Cost per head]),
,
DESC,
DENSE
),
" # "
)
This debugging measure concatenates the cost per head values along with their calculated rank, helping to pinpoint where the discrepancy lies. If the ranking does not match the expected order seen in CONCATENATEX, the issue likely stems from an incorrect filter table in RANKX. Ensuring that RANKX ranks within the correct Category rather than Area should resolve the issue.
Best regards,
Thanks for that....
My logic is that I need to rank areas within the context of the specific category.
ie within Category X; Area 1 is ranked 3 of 15.
Within Category Y; Area 1 is ranked 1 of 15.
I like the look of your debug measure as that may help lots to understand what it is actually ranking.
Thanks for that.......
Hi,
Thanks for the solutions Bibiano_Geraldo and DataNinja777 offered, and i want to offer some moreinformation for user to refer to.
hello stevejames , you can try the following.
Cost per head rank = RANKX( FILTER( ALLSELECTED('Costs'), [AreaName] in values( 'Costs'[AreaName]) 'Costs'[Total]<>0 && NOT(ISBLANK('Costs'[Total])) ), CALCULATE([Cost per head]), , DESC, DENSE )If the information cannot help you can you provide some sample data?
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.