Drilldown Then Filter Help

[Evan_Power_Bi]

I have a pie graph where there are 5 groups. Let us call them group 1 - group 5, then when i drill down in those groups I have supplier level which has multiple suppliers in each of the groups (The value field is a dollar amount). The Pie graph gets really messy as I may have 100 suppliers shown on the graph. Is there a way to filter so that only the top 50% of suppliers or maybe just the top 8-10 Within each group (this is important as if i just do topN at 10 it only does 10 supplier for the whole level) so that i can cut down on the clutter on the graph?

[jcalheir]

Hi

There is a way you can create a measure displaying the TOP N values that are in a dimension scope.

Have bellow an example for 2 dimensions, you can just add your own.

Top 8 Sales = 

var _top = 8

RETURN

    SWITCH (
        TRUE (),
        ISINSCOPE ( 'youtable'[group 2] ) && ISINSCOPE ( 'youtable'[group 1] ), 
        CALCULATE(
                [Sales],
                KEEPFILTERS(
                    TOPN(
                        _top,
                        ALL('youtable'[group 2]),
                        [Sales]
                    )
                )
            ),
            
        ISINSCOPE ( 'youtable'[group 1] ), 
        CALCULATE(
                [Sales],
                KEEPFILTERS(
                    TOPN(
                        _top,
                        ALL('youtable'[group 1]),
                        [Sales]
                    )
                )
            )
    )


 

Kind regards,
José
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

[Evan_Power_Bi]

@jcalheir I think this is on the right track... The issue is a little different though. The table is 'All Mappings' [ABC] where within [ABC] there is "group 1, group 2... etc" the groups are within the same column. look below to see if it better explains. I think your formula is in the right direction.

1. 'All Mappings' [ABC] 
2. it contains "group 1, group 2, group 3, etc"
3. drill down and you have [Supplier]
4. it contains a lot of suppliers "supplier a, supplier b, etc"
5. Depending on which group i drilldown into i was the top however many supplier by sales within that specific group.

The issue I run into is that filtering by TopN only gives the top suppliers in total and not by the group i have drilled down on.