Forum Discussion
Using a slicer on a stacked column chart
I have a stacked column chart which I've manipulated to look like a waterfall chart, to show how sales go from A to B over a period of time. The x axis is the passage of time and segments in each column chart are effectively different channels of sales in each period. I would like to introduce a slicer to isolate each channel, so if they click on a particular option, the chart only shows the sales from that channel.
I've been reading around parameter fields as an option, but I can't seem to get it to work, I've created the slicer through this method and tried to drag it into the graph visual in both the legend data field (didn't do anything) and the small multiple data field (split the graph into quadrants).
I've read that it's something to do with the parameter fields looking up against numerical data, but I can't figure out what the issue or solution is. I've tried creating separate, basic measures for each of the sales channels e.g., measure = Sum('sales'[sales channel1]) etc. and putting them into the parameter slicer instead of just the original field, but no luck there either.
PS I'm not wed to parameter fields, if there's a better solution.
Hi OllieHiggins ,
Please refers to the following steps.
Create corresponding measures for each channel and use them to create field parameters and a slicer.
Channel1 = SUM(Sheet1[Channel 1])Channel2 = SUM(Sheet1[Channel 2])Channel3 = SUM(Sheet1[Channel 3])Place the field parameter field in the Y-axis, replacing the 'Channel1', 'Channel2', and 'Channel3' fields.
The final result is as follows.
Please see the attached pbix for reference.
Best Regards,
Dengliang Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
OllieHiggins Sorry but your question is not very clear. Could you please provide more details? Read this post to get your answer quickly.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490Hi OllieHiggins
Please provide a workable sample data (not an image), your expected result from the same sample data and your reasoning behind. This will make it easier for us to figure out what you're trying to achieve.
Thanks - I have created the below test file to demonstrate the ask https://drive.google.com/file/d/1u4JpzhS99xSXmy6RMuNl3B_Pf2p9sVOv/view?usp=drive_link
Essentially, I'd like a slicer that you could click and it would show just sales channel 1, sales channel 2 etc.
The underlying spreadsheet linking to the PowerBI is as follows - the "difference" column is then made 100% transparent in the graph in PowerBI, so as to create the waterfall effect:
Sort Waterfall Data Points £ Pillars Channel 1 Channel 2 Channel 3 Difference 1 24/25 Opening Position 80000 80000 2 24/25 sales to date 2000 2000 80000 3 Current Period Closing Position 82000 82000 4 ROY sales forecast 1800 0 1800 82000 5 FY25 Forecast 83800 83800 6 25/26 1925 430 60 1435 83800 7 26/27 3553 954 360 2239 85725 8 27/28+ 5779 5394 385 0 89278 9 End State 95057 95057 Thanks - I have created the below test file to demonstrate the ask https://drive.google.com/file/d/1u4JpzhS99xSXmy6RMuNl3B_Pf2p9sVOv/view?usp=drive_link
Essentially, I'd like a slicer that you could click and it would show just sales channel 1, sales channel 2 etc.
The underlying spreadsheet linking to the PowerBI is as follows - the "difference" column is then made 100% transparent in the graph in PowerBI, so as to create the waterfall effect:
Sort Waterfall Data Points £ Pillars Channel 1 Channel 2 Channel 3 Difference 1 24/25 Opening Position 80000 80000 2 24/25 sales to date 2000 2000 80000 3 Current Period Closing Position 82000 82000 4 ROY sales forecast 1800 0 1800 82000 5 FY25 Forecast 83800 83800 6 25/26 1925 430 60 1435 83800 7 26/27 3553 954 360 2239 85725 8 27/28+ 5779 5394 385 0 89278 9 End State 95057 95057 Hi OllieHiggins ,
Please refers to the following steps.
Create corresponding measures for each channel and use them to create field parameters and a slicer.
Channel1 = SUM(Sheet1[Channel 1])Channel2 = SUM(Sheet1[Channel 2])Channel3 = SUM(Sheet1[Channel 3])Place the field parameter field in the Y-axis, replacing the 'Channel1', 'Channel2', and 'Channel3' fields.
The final result is as follows.
Please see the attached pbix for reference.
Best Regards,
Dengliang Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Brilliant, thank you!
