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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Microsoft Employee
Microsoft 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
Microsoft Employee
Microsoft 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
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.