Rank X issues

[stevejames]

I've got a RANKX solution working in a situation where a visual showing all "areas" for a single "category"

I'm now trying to show a visual with all "categories" but for a single "area" and want to see the relevant rank by category.

It's not giving me the figure I expect.. So to visdualise things I've created a measure using CONCATENATEX.

When I look at the results of the CONCATENATEX it gives me the figure in the right place as per the ordering but I cannot understand why the RANKX is giving me the value it is...

Cost per head rank = 
rankx(
        filter(
            allselected('Area'[Name]),
            'Costs'[Total]<>0 && 'Costs'[Total]<>blank()
	),    
        CALCULATE([Cost per head]),
	,DESC,
	Dense)


Cost per head rank (concat) = concatenatex(
        filter(
            allselected('Area'[Name]),
            'Costs'[Total]<>0 && 'Costs'[Total]<>blank()
        ),    
        CALCULATE([Cost per head]),
        "#",
        [Cost per head],
        DESC)

 
Any ideas gratefully received. The RANKX is the important outcome but I don't understand why CONCATENATEX is giving me essentially a different result.

[Bibiano_Geraldo]

Hi @stevejames ,

have your tried using ALL function?

Cost per head rank =
    RANKX(
        FILTER(
            ALL('Area'[Name]),  -- ignores the current filter on Area
            'Costs'[Total] <> 0 && 'Costs'[Total] <> BLANK()
        ),
        CALCULATE([Cost per head]),
        ,
        DESC,
        DENSE
    )

[DataNinja777]

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,

[stevejames]

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

[Anonymous · Accepted solution]

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.