Create a drilldown on a chart that uses a rank filter

[dapperscavenger]

Hi!

I have created a bar chart that shows top 5 and bottom 5 of this month vs. last month value.  This shows the greatest drivers of change (either positive or negatve) vs. last month.

The measures used are:

Values: MoM Market = [UoM TM Market] - [UoM LM Market]  // (this month - last month)

Axis: PH3 Description // a category field for product descriptions

Filter 1: MoM Market is not 0

Filter 2:  RankL3MoMMarket is not 0

The ranking filter measure is as follows:

RankL3MoMMarket = IF(OR([Rank MoM Market L3 Bottom]<6,[Rank MoM Market L3 Top]<6), [MoM Market],0) 

Which is made up from these two measures:

Rank MoM Market L3 Top = rankx(ALLSELECTED(SKUData[PH3 Description]),[MoM Market],,Desc, Dense)

Rank MoM Market L3 Bottom = rankx(ALLSELECTED(SKUData[PH3 Description]),[MoM Market],,Asc, Dense)

So I therefore get the top 5 and bottom 5 of change vs. last month for category L3 products

Okay, so here is the question.

I now want to put in a drilldown into the same chart.   The chart has told me what the greatest driver of change is at Level 3, so I want to drill further into that area, from Level 3 categorisation into, e.g., a Level 5 categorisation.  (example, level 3 might be "clothes" and level 5 might be "socks", "shirts" & "hats")

But every time I try to create this I end up with the chart expanding to show all the categories, not just the top and bottom 5.  example:

I've tried creating the same filter for L5, and applying the same "is not 0", but it doesn't behave like I expected  

What is the behaviour of the chart and how can I fix it?

[v-xicai]

Hi @dapperscavenger ,

You may change measure [RankL3MoMMarket] like DAX below.

RankL3MoMMarket =
IF (
    HASONEVALUE ( [Level 5 categorisation] ),
    [MoM Market],
    IF (
        OR ( [Rank MoM Market L3 Bottom] < 6, [Rank MoM Market L3 Top] < 6 ),
        [MoM Market],
        0
    )
)

Best Regards,

Amy 

Community Support Team _ Amy

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

[dapperscavenger]

ah, that did not work.  I've been googling around and I think I have asked for the impossible.  PowerBI does not seem to have this functionality.

So I will find another way to try to represent the data

Thank you!