cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KrisM
Regular Visitor

Stacked column chart based on different slicers

I am trying to create a stacked column chart with values that change depending on selections from three different slicers. 

 

My data looks like this:

KrisM_0-1626926842692.png

 

I have three slicers to select the items by category, like this:

KrisM_1-1626926960856.png

 

To get the slicers working independently (i.e. so that making a selection on one does not limit the selections on the others), I created a separate table for each of the three categories. This could be where I'm going wrong but I can't see any other way of doing it. 

 

In each of the three new tables I create a measure which is just the sum of the Values column. The measures change based on the slicer selections like I want them to, but if I add these to the values field in the stacked column chart I get this: 

KrisM_2-1626927766424.png

 

What I want is this, but I can't get the values to change with the slicer selections: 

KrisM_3-1626927920577.png

 

I can add the measures to the values field in a clustered column chart and they change with the slicer selections. This is close but not really what I want: 

KrisM_0-1626929689154.png

 

I tried creating a Summary table with the sum of values for each category from the three new tables. This allows me to create the kind of graph I want but again the values don't change with the slicer selections. I've tried several variations of the code below to get the sums in the Summary table, for example using the fliter and calculate formulas. It will only ever give the sum unaffected by the slicer selections. I assume table values don't change dynamically like measures do? Is there any other way of doing this? 

 

 

Sum by category = 
if(Summary[Category] = "Category 1", sum('Category 1'[Value]),
if(Summary[Category] = "Category 2", sum('Category 2'[Value]),
if(Summary[Category] = "Category 3", sum('Category 3'[Value]))))

 

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@KrisM , You have new table Summary and category has row values category 1, category2 etc

 

Then this should work as measure with Category from the summary on the axis

 

Sum by category = Switch( True()
Max(Summary[Category]) = "Category 1", sum('Category 1'[Value],
Max(Summary[Category]) = "Category 2", sum('Category 2'[Value]),
Max(Summary[Category]) = "Category 3", sum('Category 3'[Value])
)

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

3 REPLIES 3
amitchandak
Super User IV
Super User IV

@KrisM , You have new table Summary and category has row values category 1, category2 etc

 

Then this should work as measure with Category from the summary on the axis

 

Sum by category = Switch( True()
Max(Summary[Category]) = "Category 1", sum('Category 1'[Value],
Max(Summary[Category]) = "Category 2", sum('Category 2'[Value]),
Max(Summary[Category]) = "Category 3", sum('Category 3'[Value])
)

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Sorry just saw you said it should be a measure not a column... That worked, thanks. 

Thanks, but this seems to return the sum of category 3 for all categories. I tried removing the max function and it gave the sum of each category without changing with the slicers. Is there something more that would need to go in the switch expression? 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors