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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Super User

@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])
)

 

 

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@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])
)

 

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.