Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Dear Power BI community,
I have a Sales table with columns of Stores, Article, Month and Sales, I also have the corresponding dimension tables for Stores and Articles. I would like to measure the performance of specific articles, but only considering the sales from the stores that actually sells those articles. So I need to keep the sales from all articles, from that subset of stores.
Here is an example...
My data (very simplified) looks like this:
Store | Article | Month | Sales |
Downtown | Soda | January | 10 |
Downtown | Chips | January | 25 |
Downtown | Candy | January | 32 |
Downtown | Milk | January | 23 |
South | Chips | January | 65 |
South | Candy | January | 27 |
South | Milk | January | 28 |
South | Cookies | January | 38 |
North | Soda | January | 2 |
North | Candy | January | 40 |
North | Milk | January | 13 |
North | Juice | January | 11 |
West | Soda | January | 23 |
West | Chips | January | 34 |
West | Candy | January | 45 |
Let's say I choose in a slicer the Articles "Juice" and "Cookies". Those two articles have sales in stores "South" and "North", so I would expect to get a table like this:
Store | Article | Month | Sales |
South | Chips | January | 65 |
South | Candy | January | 27 |
South | Milk | January | 28 |
South | Cookies | January | 38 |
North | Soda | January | 2 |
North | Candy | January | 40 |
North | Milk | January | 13 |
North | Juice | January | 11 |
The Article selection must be dynamic to see the sales f
How can I do this in Power BI? I've been trying for hours trying to create new tables, but I haven't been successful.
Thank you so much in advance for the help.
Solved! Go to Solution.
Hi javierzamb,
You need to create a seperate dimension with both Stores and articles, connect it with store dimension (with both sided relationship) in order to do so
Store-Article Dimension:
and the relationships:
and then you can add a slicer with the article column of the Store-Article Dimension to get your result:
My initial approach was something "similar" calculating a table, but this one actually works! I still need to test it when using huge tables, but looks promising. Thanks!
Hi javierzamb,
You need to create a seperate dimension with both Stores and articles, connect it with store dimension (with both sided relationship) in order to do so
Store-Article Dimension:
and the relationships:
and then you can add a slicer with the article column of the Store-Article Dimension to get your result:
Didn't think about this approach. I just tested it with the real databases and works great. Thanks!
User | Count |
---|---|
93 | |
85 | |
76 | |
66 | |
62 |
User | Count |
---|---|
112 | |
97 | |
95 | |
64 | |
58 |