RANKX Question Help Needed

[Anonymous]

So I have a data set of locations with their corresponding values and I want to be able to rank them across the whole company and then also across their geographic regions and also their size categories. 

I have three tables "Values" connected to "Org Mapping" connected to "Org Sizing".

I have the following measures:

Value Measure = SUM('values'[Value])

Value Rank Across company = 
CALCULATE (
    IF (
        HASONEVALUE ( 'values'[Value] ),
        IF (
            NOT ( ISBLANK ( 'values'[Value Measure] ) ),
            RANKX (
                FILTER (
                    ALL ( 'Org Mapping'[Org ] ),
                    NOT ( ISBLANK ( 'values'[Value Measure] ) )
                ),
                'values'[Value Measure],
                ,
                ,
                DENSE
            )
        ),
        BLANK ()
    ),
    ALL ( 'Org Mapping'[Geo]),
    ALL ( 'Org Sizing'[Size Cat 1], 'Org Sizing'[Size Cat 2] )
)

Value Rank Across Selection = 
IF (
    HASONEVALUE ( 'values'[Value] ),
    RANKX (
        FILTER ( ALL ( 'Org Mapping'[Org ] ), 'values'[Value Measure] ),
        'values'[Value Measure],
        ,
        ,
        DENSE
    ),
    BLANK ()
)

The users should be able to select on Size category 1, Size category 2, and Geo to see where the locations rank within those groups but also be able to see where those locations rank across the whole company. I have also added a location selector so the user can go directly to a specific location if they choose. 

I am having two issues:

1. When a selection is made directly to a location it displays the same value for across selection and across company. I understand why this is happening. The user needs to select something for Size cat 1, 2, and geo for that measure to work properly. Is there anything I can do to force that measure to automatically rank across the three selections? Each location has a unique combination of size categories and geo.

2. I noticed that once my value goes negative the rankings for the one measure seem to be off. I have no idea why this is happening. 

 

The Github link below has the randomized data I have been using to work on this as well as the PBIX. Any help is appreciated. Thank you.

https://github.com/DerekAG92/RANKX-Help

[v-lili6-msft]

hi  @Anonymous 

1.  you could try to use ALLSELECTED instead of ALL in the formula

https://powerbi.istanbul/all-allselected-ve-rasyolar/

2. You could just use this simple formula

Value Rank Across Selection = IF (
    HASONEVALUE ( 'values'[Value] ),
    RANKX (
       ALLSELECTED(  'Org Mapping'[Org ] ),
        'values'[Value Measure],
        ,
        ,
        DENSE
    ),
    BLANK ()
)

Regards,

Lin

[Anonymous]

So I was able to fix the rankings not being the same by changing the Value Rank Across Selection measure to being this:

Value Rank Across Selection = 
IF (
    HASONEVALUE ( 'values'[Value] ),
    IF (
        NOT ( ISBLANK ( 'values'[Value Measure] ) ),
        RANKX (
            FILTER (
                ALL ( 'Org Mapping'[Org ] ),
                NOT ( ISBLANK ( 'values'[Value Measure] ) )
            ),
            'values'[Value Measure],
            ,
            ,
            DENSE
        )
    ),
    BLANK ()
)

I dont really understand what is happening but it seems to be working. I still am not sure how to accomplish my other question.