Hierarchy Ranking with slicer and drilldown in bar chart

[TejasMahind123]

Hi all,

I am facing an issue when depicting Groups and Subgroups with top N functions when I'm introducing the year(2022,2023) legend. 
Scenario 1 : Groups and subgroups with No legends

The bars are depicted correctly as we adjust the dyanmic slicer linked to the visual. After drilling down also we are able to see the same number of bars which we have in slicer selection. 

Subgroup level:

However if I introduce Year column in legend, the visual is depicting incorrect bars

Here ideally only 2 top groups i.e. ABC and LMO with their corresponding year values should be visible. Sharing the dax code measures passed in the chart below:

Table name: demoRank

1. To calculate the rank:

Rank visual for costs =

IF (

    ISINSCOPE ( demoRank[Subgroup] ),

    RANKX (

        CALCULATETABLE (

            VALUES ( demoRank[Subgroup] ),

            ALLSELECTED ( demoRank[Subgroup])

        ),

        [Costs]

    ),

    IF (

        ISINSCOPE ( demoRank[Group]),

        VAR Groupcost = [Costs]

        RETURN

            CALCULATE (

                RANKX (

                    VALUES ( demoRank[Group] ),

                    [Costs],

                    Groupcost

                ),

                ALLSELECTED(demoRank[Group])

            )

    )

)

2. To calculate Top N values dynamically, this measure is passed in Y axis

Top N cost = IF([Rank visual for costs]<='Top values'[Top values Value],MeasuresTable[Costs])

3. Group and subgroup hierarchy is passed in X axis

Let me know if any other clarifications are required

[TejasMahind123]

Hi v-zhangti

Just wanted to follow up and check if you can suggest a solution for this. Thanks in advance!

[TejasMahind]

Hi v-zhangti,
Thanks for the response. Please find the clarifications below:

1. Before adding year legend, the dax measure is working fine before and after drilling down the bars with dynamic top n values, see image below:

2. After adding year legend and selecting '2' as top N values, the top values for 2022 and 2023 are calculated seperately. However I want to have the the groups with top 2 values for 2023 and display their adjacent 2022 values. 

Please find the link to the pbix file here, kindly refer to 'with legend' tab wherein I'm facing the issue https://drive.google.com/file/d/1qaO_CFSWZco8dXfquxV6c2dZghObydzs/view?usp=sharing

[TejasMahind]

Hi v-zhangti, 
Please ignore the spacing gap between two bullets in my above reply😄

[v-zhangti]

Hi, @TejasMahind123 

Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures or Excel. I look forward to your response.

Best Regards,

Community Support Team _Charlotte

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