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 spe...
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

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.