Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I'd like to use a slicer to show all the customers on a given day who bought a certain product, but include all the other product sales in the total. ie, select all the ones that contain a value, and not filter out the others.
Sample example data
Date | Customer | Color | Item | Payment |
1/1/2019 | John | Red | Shirt | 1 |
1/1/2019 | John | Red | Shoes | 3 |
1/1/2019 | Betty | Blue | Shirt | 2 |
1/1/2019 | Betty | Blue | Shoes | 3 |
1/1/2019 | Betty | Blue | Pants | 4 |
1/1/2019 | Mike | Blue | Shoes | 3 |
1/1/2019 | Mike | Blue | Pants | 4 |
1/2/2019 | John | Blue | Shirt | 1 |
1/2/2019 | Betty | Blue | Shoes | 3 |
1/2/2019 | Betty | Blue | Pants | 4 |
1/3/2019 | Peter | Blue | Shirt | 2 |
1/3/2019 | Peter | Red | Shirt | 2 |
1/3/2019 | John | Red | Shoes | 3 |
1/3/2019 | John | Red | Pants | 4 |
So, if I used a slicer with Shirt - Shoes - Pants, and chose "Shirt", the total for 1-1-19 would be all the payments for John and Betty, but not Mike (he didn't buy a shirt on 1-1-19). Total would be $13, and if I put the name into the visual it would show $6 for John, $7 for betty on this day.
ON 1-2 it would include John alone and show $1 etc.
AND I could then show a table that looks like this
Date | Customer | Color | Item | Payment |
1/1/2019 | John | Red | Shirt | 1 |
1/1/2019 | John | Red | Shoes | 3 |
1/1/2019 | Betty | Blue | Shirt | 2 |
1/1/2019 | Betty | Blue | Shoes | 3 |
1/1/2019 | Betty | Blue | Pants | 4 |
1/2/2019 | John | Blue | Shirt | 1 |
1/3/2019 | Peter | Blue | Shirt | 2 |
1/3/2019 | Peter | Red | Shirt | 2 |
That shows all the details but only if there was a shirt purchase in there.
I saw somewhere months ago about using a slicer on a table that was not linked to the other tables would allow for a slicer to be used this way, but I can't find the post/blog or recreate the syntax from memory.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
18 |