Forum Discussion

dkennedy's avatar
dkennedy
Frequent Visitor
6 years ago
Solved

Top N stacked columns by column

Hello,

 

I am having trouble with a visualization and I'm hoping to get some assistance.

 

I am trying to create a stacked column chart that only shows the top N spending categories by amount spent, but I want the top N to be per facility, not overall.

In my example, there are 4 facilities (G1-4) with 7 spending categories (A-G), and I would like to display the top 3 (and "other" if possible) spending categories by total spending per facility (The example is specific but I am hoping for a general solution I can apply to similar datasets).

Currently when I use the top N filter, Power BI determines which spending categories have the greatest total spending overall, but not on a per facility basis. For example, Type D transactions account for the most total spending across all facilities, but the top 3 transaction types for facility G1 are A, B, and E so I would like the G1 stacked column to be broken up into A, B, and E. 

 

I have attached a screenshot of my current stacked column chart, which is has an overall top N filter (not what I'm looking for), as well as my sample data in a table.

 

 

I would also need this to work with drill down, i.e. G1 can be broken up into smaller subgroups and I would like to display each of their top 3 spending categories as well.

 

Is this type of visualization possible? Please let me know if I should provide additional information.

 

Thanks

Need Help
Tips and Tricks
Tutorial Requests
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    Please try this expression in your stacked chart to get your desired result.

     

     

    Top 3 New =
VAR __thiscat =
    VALUES ( Spend[Category] )
VAR __thisfacility =
    SELECTEDVALUE ( Spend[Facility] )
VAR __top3thisfacility =
    TOPN (
        3,
        ALL ( Spend[Category] ),
        CALCULATE ( SUM ( Spend[Total Spending] ), Spend[Facility] = __thisfacility )
    )
RETURN
    CALCULATE (
        SUM ( Spend[Total Spending] ),
        INTERSECT ( __thiscat, __top3thisfacility )
    )

     

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

3 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    Please try this expression in your stacked chart to get your desired result.

     

     

    Top 3 New =
VAR __thiscat =
    VALUES ( Spend[Category] )
VAR __thisfacility =
    SELECTEDVALUE ( Spend[Facility] )
VAR __top3thisfacility =
    TOPN (
        3,
        ALL ( Spend[Category] ),
        CALCULATE ( SUM ( Spend[Total Spending] ), Spend[Facility] = __thisfacility )
    )
RETURN
    CALCULATE (
        SUM ( Spend[Total Spending] ),
        INTERSECT ( __thiscat, __top3thisfacility )
    )

     

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

    • dkennedy's avatar
      dkennedy
      Frequent Visitor
      6 years ago

      Thank you mahoneypat ! This works and even works in when I drill down facility! The below solution from v-juanli-msft also appears to work but I appreciate this one being a simple formula in a single measure.

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi dkennedy 

    Measures

    sumtotal =
CALCULATE (
    SUM ( 'Table'[Spending] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Category] = MAX ( 'Table'[Category] )
            && 'Table'[Facility] = MAX ( 'Table'[Facility] )
    )
)

RANK = RANKX(FILTER(ALLSELECTED('Table'),'Table'[Facility]=MAX('Table'[Facility])),[sumtotal],,DESC,Dense)

Measure = IF([RANK]<=3,SUM('Table'[Spending]))

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.