Forum Discussion

Evan_Power_Bi's avatar
Evan_Power_Bi
Frequent Visitor
3 years ago

Drilldown Then Filter Help

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?

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Evan_Power_Bi
      Frequent Visitor
      3 years ago

      Anonymous 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.