How to get an accurate ranking unaffected by legend for a measure?

[charonT]

Hi all,

I have a scatter chart and would like to create a measure to calculate the ranking for each dot. Y axis is [C] and x axis is [YoY change]. While I would like to calculate a YoY change, I did not create a relationship between a 'year'[year] and 'C'[year]. And the legend is classified by another table 'Provider list'[class], which has a relationship with 'C' to connect the Provider information. I got wrong results for that, obviously the ranking and the number of total provider are wrong.

This is the slicer:

Again, please note the 'Year'[Year] does not have a relationship with the 'C'[Year].

This is the ranking DAX which does not work: 

RankingLabel_C = rankx(ALLselected('C'), [C],,DESC)&"/"&COUNTX(ALLselected('C'), [C])

The ranking should refer to the ranking of C out of all providers in the selected year, regardless of the 'Provider list'[class].

This is the DAX of [C] (Y axis and the measure for ranking):

C =

    DIVIDE(

        SUMX(

            FILTER('C', 'C'[Year] = SELECTEDVALUE('Year'[Year])),

            'C'[Ced]

        )*100,

        SUMX(

            FILTER('C', 'C'[Year] = SELECTEDVALUE('Year'[Year])),

            'C'[Denominator_C]

        )

    )

This is the DAX of [YoY change] (X axis):

YoY_change =

VAR CurYear = SELECTEDVALUE('Year'[Year])

VAR C_Cur =

    DIVIDE(

        SUMX(

            FILTER('C', 'C'[Year] = CurYear),

            'C'[Ced]

        )*100,

        SUMX(

            FILTER('C', 'C'[Year] = CurYear),

            'C'[Denominator_C]

        )

    )

VAR PreYear = CurYear - 1

VAR C_Pre =

    DIVIDE(

        SUMX(

            FILTER('C', 'C'[Year] = PreYear),

            'C'[Ced]

        )*100,

        SUMX(

            FILTER('C', 'C'[Year] = PreYear),

            'C'[Denominator_C]

        )

    )

RETURN

    C_Cur - C_Pre

Any suggested DAX or approaches to return the correct ranking?

please let me know if the information is not enough. Thank you in advance.

Hope everyone have a good day.

[charonT · Accepted solution]

Thank you. I resolved the issue on my own. It turns out I had set the wrong ALLSELECTED target table. The correct DAX formula should be:

RankingLabel_C = RANKX(ALLSELECTED('Provider list'), [C],,DESC) & "/" & COUNTX(ALLSELECTED('Provider list'), [C])

[lbendlin]

They recently added the option for legend sorting.  So this might be possible.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...