Forum Discussion
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...
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
mahoneypat
Microsoft Employee
Microsoft EmployeePlease 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
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.