Forum Discussion
How to make a selective Matrix Table
Hi,
I basically have this problem where i have a data sheet of 3 main tabs:
1. Top 20 stores and their volume each month
2. The top 5 customers each of those 20 stores and how they have contributed total
3. The monthly volume breakdown of those top 5 customers
I want to make a matrix table where it shows each stores overall volume (tab 1), which then dropdown to show the top 5 customers and their contributive volume (tab 2) and then the monthly breakdown of their volume contribution (tab 3).
But i seem to be stuck on the tab 2 step where the volume value does not change from the tab 1 volume and cant seem to figure out the relationships. The yellow highlighted text below show values that should be changed but cant and are stuck on store no.1's volume. In my sheet - I've even added a tab for each individual store, unique customer and a date table in the power bi file.
I have attached my sheet and current power bi file if someone can give me a hint, thanks:
Hiii derekli1700
1. Check Relationships in the Data Model
- Your Stores table (Tab 1) should have a one-to-many relationship with the Customers table (Tab 2), linking via Store_ID.
- The Customers table should have a one-to-many relationship with the Monthly Breakdown table (Tab 3) via Customer_ID.
- Ensure all these relationships are set correctly and set to Single Direction (or Cross-filtering enabled if necessary).
2. Check Aggregation in Your Measure
- If you are using SUM([Volume]), the data might not be filtering down correctly. Try using:This ensures that when you drill down into a store, the sum is dynamically recalculated based on the customers under that store.DAXCopyEditTotal Volume = SUMX(VALUES(Customers[Customer_ID]), SUM(MonthlyBreakdown[Volume]))
3. Verify That Customer Data is Unique Per Store
- If Customer_ID appears under multiple stores in your dataset without a proper Store_ID filter, the volume calculation might not be filtering down correctly.
4. Test Table Filters Using a Simple Table Visual
- Add a simple table visual with Store, Customer, and Volume fields.
- If all customers are still showing the same volume as the store, it confirms that filtering is not working.
2 Replies
Hiii derekli1700
1. Check Relationships in the Data Model
- Your Stores table (Tab 1) should have a one-to-many relationship with the Customers table (Tab 2), linking via Store_ID.
- The Customers table should have a one-to-many relationship with the Monthly Breakdown table (Tab 3) via Customer_ID.
- Ensure all these relationships are set correctly and set to Single Direction (or Cross-filtering enabled if necessary).
2. Check Aggregation in Your Measure
- If you are using SUM([Volume]), the data might not be filtering down correctly. Try using:This ensures that when you drill down into a store, the sum is dynamically recalculated based on the customers under that store.DAXCopyEditTotal Volume = SUMX(VALUES(Customers[Customer_ID]), SUM(MonthlyBreakdown[Volume]))
3. Verify That Customer Data is Unique Per Store
- If Customer_ID appears under multiple stores in your dataset without a proper Store_ID filter, the volume calculation might not be filtering down correctly.
4. Test Table Filters Using a Simple Table Visual
- Add a simple table visual with Store, Customer, and Volume fields.
- If all customers are still showing the same volume as the store, it confirms that filtering is not working.
Hi derekli1700 ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
