Above you will see 3 tables: Profit Center, Commit Bridge, & Commit Data. Please note that ONE “Commit Manager” can have MULTIPLE “Profit Centers.”
- Note: Profit Centers – 15680 & 15681 both roll up to “Small East – Wall”
The vast majority of our reporting is done at the “Profit Center” level, which means we have countless tables where we NEED to use slicers that are chosen from the Profit Center table. [PROBLEM!!!]
We created a bridge table within PowerPivot called, Commit Bridge. This table is made up of a unique list of “Commit Managers,” since the Profit Center table has repeated “Commit Managers.” This table is used to connect the relationship between Profit Center and Commit Data.
What’s Broke??
Our pivot table within PowerPivot, using columns selected from the Commit Data table, DOES NOT change when we use the pre-built slicers (chosen from Profit Center table). The data remains static.
- PowerPivot is not recognizing the relationship between “Commit Manager” and “Profit Center.”
END GOAL
If a Profit Center slicer is used, our Commit Data pivot table should recognize that while it DOES NOT have values down at the “Profit Center” level, the roll-up of those ARE the values associated with each “Commit Manager.” We would like to fix this problem within the TABLES rather than using a New Measure within the Pivot.
