Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
I have three slicers to select the items by category, like this:
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:
What I want is this, but I can't get the values to change with the slicer selections:
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:
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]))))
Solved! Go to Solution.
@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])
)
@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?
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |