Using a slicer on a stacked column chart

[OllieHiggins]

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.

[Anonymous · Accepted 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.

[OllieHiggins]

Brilliant, thank you!

[OllieHiggins]

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

[OllieHiggins]

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

[danextian]

Hi @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.

[parry2k]

@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/38490