Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
dkennedy
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
mahoneypat
Employee
Employee

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! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

3 REPLIES 3
v-juanli-msft
Community Support
Community Support

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.

mahoneypat
Employee
Employee

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! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors