Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
PPilgrim
Frequent Visitor

Comparing measure of a user SelectedValue against the group allowing the user to shape the group

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.

StoreNum.jpg

 Diagram of desired function

visual diagram.jpg


Thank you in advance for help!!




2 REPLIES 2
PPilgrim
Frequent Visitor

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.

lbendlin
Super User
Super User

 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 ?

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.