Excel Power Pivot Data Model

Question

I'm working on what I assumed would be a fairly straightforward data model in Excel (not Power Bi). I have three distinct tables (Parts, Equipment, and Configurations). Equipment is made up of parts, and configurations are made up of equipment. Then, I have two "allocation" tables that allocate Equipment to Configurations and Equipment to Parts.

See the picture in the attachment. This includes the unprocessed tables, the relationship from the data model, and the flawed pivot table.

I'm not obtaining the right values in the pivot table when I connect everything in the data model. I'm rather certain that Excel's data model's one-direction restriction is what's causing the problem. Exists a more effective method to model this data? or is there an alternative? I've read about cross filters, but do I need to create one for each field in my tables?

Answer 1

Here is the answer for your question: https://www.contextures.com/excelpivottableerrors.html