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.
My live dataset is fairly large and complex but I've boilded down my issue to a simple example. I have two tables shown below.
One ('stores') contains a list of retail store numbers all of which roll up to a Parent store. The 'stores' table also contains facts about each store such is whether or not the store is online only.
'Stores' connects via a 1 to many relationship to a 'sales' table on the shared [StoreNum] column. The sales table is by StoreNum and includes departments and total sales of all store numbers and all parents. Not all stores offer all departments.
What i'm trying to do seems fairly straight forward. I want the user to be able to select the Parent store from a drop down slicer based on values('stores'[parent]). That selection will then filter the first visual to show the sales from each department offered among all of the Parents different stores.
In the second visual, I want to show the aggregate of all sales from all stores in each department where that department matches a a department under the selected value Parent store. I also want to provide slicers such as one for 'stores'[onlineonly] that the user can use to shape the peer group being compared to. These selections will affect the total sales counts in visual 2 leaving everything else unaffected.
I have tried a variety of approaches to this all with varying degress of success. The simplest approaches can get everything working except with all departments from all stores in the 'sales' table in visual two. I need to only show the departments relevant for comparing to the selected value.
Diagram of desired function
Thank you in advance for help!!
Thank you for the super thoughtful response but the requirements are quite a bit more complex than that as detailed in my request for help. I've since resolved it on my own. I needed to be able to control where in the calculations the slicer filters are applied. I created a disconnected table for a slicer and then manually applied the filter. I created a specific measure for both the selected and the peer group. The removefilters function was used but not on that column.
There are probably easier/better ways to achieve this but this worked for me.
I want to show the aggregate of all sales from all stores in each department where that department matches a a department under the selected value Parent store.
Sounds like you want to do a REMOVEFILTERS() on the 'Stores'[StoreNum] column ?
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 |
---|---|
97 | |
95 | |
80 | |
77 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |