Rank to show monthly and weekly top 5 using drill down/up

[Banistas]

Hi,

I'm trying to create a stacked column chart to display the top 5 legends with the highes volumes for each week, and then using drill up to also get what they are for each month. 

The challenge I'm finding is that this works well at the weekly level, but when I drill up to the month it seems to add all the legends from the weeks in that month. So, for example, Let's say that week 51 and week 52 are part of December. Then let's asume that:

For week 51 my top 5 legends were yellow, light blue, dark blue, green, and red.   

For week 52 my top 5 legends were yellow, light blue, dark blue, red, and grey.

What's happening then, is that when I drill up:

December shows all 6 reasons displayed on weeks 1 and 2 showing yellow, light blue, dark blue, green, red, and red.

This is not ranking at the month level, but at the weekly level and then it seems to be keeping this when it goes a level up. 

Here is a quick sample of what I see on the chart:

Weekly ranking:

Monthly ranking:

The way I'm doing the top 5 selection, is by having a rank formula which is as follows:

Rank Legends = RANKX ( ALL ( 'Appointments Table'[Legend] ), Volume )

And then using this formula to restrict to when its value is 5 or less.  

Note that I have also noticed that the chart works fine if I change the axis so that the user can drill up to go from the months to the weeks. However, this does not work as it's inconsistent with the set up in all of our other reports where you always need to drill up to go from the weeks to the months, so this unfortunately doesn't work as a solution for me. 

Does anyone have an ideas of what solution I could implement to acomplish what I'm after?

Thanks. 

[v-lionel-msft · Accepted solution]

Hi @Banistas ,

Please refer to the formulas.

//Column
__Rank by week = 
RANKX(
    FILTER( Sheet7, Sheet7[Week] = EARLIER(Sheet7[Week])),
    [Sales],
    , DESC,Dense
) 

//Column
__Rank by month = 
RANKX(
    FILTER( Sheet7, Sheet7[Month] = EARLIER(Sheet7[Month]) ),
    [Sales],
    , DESC, Dense
)

//Column
Measure = 
VAR x = 
CALCULATE(
    SUM(Sheet7[Sales]),
    FILTER(
        Sheet7,
        Sheet7[__Rank by week] <= 5
    )
)
VAR y = 
CALCULATE(
    SUM(Sheet7[Sales]),
    FILTER(
        Sheet7,
        Sheet7[__Rank by month] <= 5
    )
)
RETURN
IF(
    HASONEFILTER( Sheet7[Week]),
    x, y
)

Since I don't know which visual you are using, no renderings are given at the end.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Banistas , as This dynamic Rank so it will change based on the level other than legend. Top five legend inside others .

do you want to fix the rank at month level and then use in drill ?

[Banistas]

@amitchandak I would like to display only 5 legends (the top 5) at each level (monthly and weekly). 

I'll elaborate on my example to add some values to make it clearer. Let's assume these are my volumes:

	Dark Blue	Light Blue	Green	Yellow	Red	Grey	Purple	Brown	White
Week 51	20	12	6	6	1	9	1	0	2
Week 52	21	11	2	6	5	8	2	1	0
December	41	23	8	12	6	17	3	1	2

What I want the chart to display when I drill up to the month, are the top 5 legends for the month (dark blue, light blue, green, yellow, and grey). However, the chart is displaying all 6 legends that I had in the top 5 for the weeks below combined. So, even though Red is not a top 5 volume for December (it's ranked as the 6th volume) it seems to be included in the chart just because it's part of the top 5 volumes from week 52.

I hope this helps clarify what the sitruation is and what I am after. Thanks.