Forum Discussion
Difference in Sales between two staff from the same dataset
I have a table with a structure similar to this:
| Staff | Product | Year | Sales |
| A | P1 | 2000 | 350 |
| A | P1 | 2001 | 267 |
| A | P2 | 2000 | 304 |
| A | P2 | 2001 | 250 |
| B | P1 | 2000 | 245 |
| B | P1 | 2001 | 263 |
| B | P2 | 2000 | 320 |
| B | P2 | 2001 | 210 |
| C | P1 | 2000 | 280 |
| C | P1 | 2001 | 235 |
| C | P2 | 2000 | 354 |
| C | P2 | 2001 | 311 |
I want to create a visualisation to compare the difference in sales by product over time for two selected staff. The staff will be selected via two separate filters, for example: I will select Staff A in Filter #1 and Staff B in Filter #2.
The outcome I'm seeking for will be a stacked bar chart with the values being the difference in sales quantity (i.e. Sales of Staff selected in Filter #1 minus sales of Staff selected in Filter #2). The legend will be the Product and x-axis being the Year.
Your guidance is much appreciated.
Did you mean stacked column chart?
Not sure this visual type is helping you?
5 Replies
- lbendlin
Super User
Did you mean stacked column chart?
Not sure this visual type is helping you?
This is exactly what I was looking for. Thank you!
pakdilip , Is this something you are looking for?
- Ashish_Mathur
Hi,
PBI file attached.
Hope this helps.
- Odet_Maimoni
Advocate I
Yes, this should be possible.
A common way to handle this is to create two separate disconnected tables for Staff selection, so each slicer controls a different staff member independently.
Then create measures like:
- Sales for Staff selected in slicer 1
- Sales for Staff selected in slicer 2
- Difference = Staff 1 Sales - Staff 2 Sales
You can then use that difference measure in a stacked bar/column chart, with Year on the axis and Product on the legend.
The key point is that the two staff slicers should come from separate disconnected tables, otherwise both slicers will affect the same staff field and the comparison will not work correctly.
