cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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.

dkennedy_0-1593523152130.png

 

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User VI
Super User VI

Re: Top N stacked columns by column

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
Highlighted
Super User VI
Super User VI

Re: Top N stacked columns by column

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Community Support
Community Support

Re: Top N stacked columns by column

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]))

Capture15.JPG

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.

Highlighted
Frequent Visitor

Re: Top N stacked columns by column

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.

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors