Top N stacked columns by column

[dkennedy]

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

[v-juanli-msft]

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.

[mahoneypat · Accepted solution]

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]

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.