RANKX to separately graph each rank <= 5

[cs_rob]

Hi all,

I'm just beginning the DAX learning process so my apologies if this has been addressed elsewhere, despite my best attempts to find a solution myself.

I've created a visualisation of the top 5 GL codes by amount for each region.

I'd then like to graph the amount for each of the top 5 GL codes on separate line charts over a period of 2 years.

Top N is currently displaying as expected (please see below screenshot), but I'm struggling to solve how to apply ranking correctly so I can use this in a visualisation filter for each line chart.

My best attempt at a calculated measure was:

Rank = RANKX(
               ALLSELECTED('Discretionary spend'[ACCOUNT_NAME]),
                  CALCULATE(
                     SUM('Discretionary spend'[AMOUNT])
                   )
             )

The above measure works fine when a time period is not introduced, and appears to display each rank correctly on a bar chart.

However, when adding a MONTHKEY field to a line chart it displays multiple GL codes, due to the fact that the above measure separately considers the aggregate of each GL code and MONTHKEY rather than solely the aggregate of the GL code.

If I was to filter my data for Nov to Dec-17, the line chart would plot each item highlighted in yellow below, when I would prefer it to plot solely values relating to GL code 1, as it has the highest aggregate sales over the filtered period.

Regards,

cs_rob

[cs_rob · Accepted solution]

For anyone else encountering the same issue:

I did manage a workaround that is far from elegant or concise, and I have no doubt there are much better solutions available. I created a separate table grouped on the original table then joined the two via a composite key. I then used a RANKX measure:

RankAccount = RANKX(
            ALLSELECTED(Region_Summary[D_AccountName]),
                CALCULATE(
                    SUMX(Region_Summary,Region_Summary[Total Amount])
                )
        )

Each line chart was then based on the amount and monthkey from the original table, and the accountname from the groupedby table. The rank measure was added to the visual filter for each of the 5 graphs and then set to "is 1" for rank 1, "is 2" for rank 2 etc.

Regards,

cs_rob

[v-chuncz-msft]

@cs_rob,

Thanks for sharing the workaround. You may accept the solution above. Your contribution is highly appreciated.