Top 5 filtering not showing correctly

[Anonymous]

Hi,

I am trying to create a column chart to show the top 5 customers depening on the quarter. However I noticed something strange. The top 5 filtering works just fine, but only applies to the last quarter. 

In the pictures you see that the Top5 for qtr 1 2018 should be customer 1, 2, 3, 4 and 5. However as they are not the Top 5 in the last visible quarter, Power BI ommits them and shows the last Top 5 customers also in the first visible quarter.

Is there a way, or measure, to always show the top 5? Even if that means the legend will display more than 5 customers?

Thank you very much!

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

To create a calculated column as below and make it to be greater than or equal to 5.

rank = 
RANKX (
    FILTER ( 'Table', 'Table'[date] = EARLIER ( 'Table'[date] ) ),
    CALCULATE (
        SUM ( 'Table'[values] ),
        ALLEXCEPT ( 'Table', 'Table'[date], 'Table'[customer No] )
    ),
    ,
    ASC,
    DENSE
)

[Anonymous]

Hey @v-frfei-msft ,

Thank you very much for your reply. It works indeed. I was also wondering, whether it is possible to add an additional argument/ attribute. For example a city or country?

[v-frfei-msft]

Hi  @Anonymous ,

To add the city or country in ALLEXCEPT.

rank = 
RANKX (
    FILTER ( 'Table', 'Table'[date] = EARLIER ( 'Table'[date] ) ),
    CALCULATE (
        SUM ( 'Table'[values] ),
        ALLEXCEPT ( 'Table', 'Table'[date], 'Table'[customer No],[CITY],[COUNTRY] )
    ),
    ,
    ASC,
    DENSE
)

If it doen't meet your requirement, kindly share your sample data to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.