I've forgotten something basic (or it cant be done!)

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
2,184
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have two tables a list of budgets, and a list of items against which those budgets are spent. There is a one to many relationship between the Budget table categories and the categories in the spend table and the two fields are linked in the Powerpivot Manager. The problem is that while the pivot table lists the budgets correctly it then lists every item against each budget element, which is incorrect. The source tables and the output pivotable are shown below.

The two measures are defined as:

'Test Budget Amount' =SUM(tblBudget[Budget])
'Test Detail Spend' =CALCULATE(SUM(tblDetail[Spend]),tblBudget)

At present I'm not worried about the repeating Budget figure, though it would be better if it could be suppressed when expanding the budget entry to show the items. I think I'm doing something really simple incorrectly, but ....

Any advice grateful received.

1738798730629.png


Trustee Board - Financial Summary v7.xlsx
IJKLMNO
17tblBudgettblDetail
18CategoryBudgetCategoryDescriptionSpend
19Budget1100Budget1Item1100
20Budget220Budget1Item2200
21Budget3300Budget1Item3300
22Budget3Item4400
23Budget3Item5500
24
25Pivot Table
26Row LabelsTest Budget AmountTest Detail Spend
27Budget1
28Item1£100.00£100.00
29Item2£100.00£200.00
30Item3£100.00£300.00
31Item4£100.00
32Item5£100.00
33Budget2
34Item1£20.00
35Item2£20.00
36Item3£20.00
37Item4£20.00
38Item5£20.00
39Budget3
40Item1£300.00
41Item2£300.00
42Item3£300.00
43Item4£300.00£400.00
44Item5£300.00£500.00
45Grand Total£420.00£1,500.00
Summary
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Filtering only works from 1 to many, not the other way round, so every item has a value for every budget. You could use RELATED to pull the budget values into a new column in the detail table and then use that for your measure, and you should get the results you expect.
 
Upvote 0
Hi Rory, The first Row Label in the Pivot Table Field List is the category taken from the tblBudget, the description field is then taken from tblDetail. So in my interpretation tblBudget should be filtering tblDetail, a 1 to many relationship. I’m 99% certain a structure like this should work as I’ve done it before.
 
Upvote 0
It would work if you weren't using a measure on a dimension table. The only filtering in play for the measure is for the Budget name, so it returns a value for every item in the detail table. If you use RELATED to pull the budget value into the detail table, then the problem will go away because there will only be values for the relevant items.
 
Upvote 0
What you need to remember is that relationships do not work like joins in a database. Your starting point for the pivot table (as you will see if you remove all the measures) is a cartesian product of the various fields:
1738834762601.png


What then happens is that for any combinations of fields where the measure(s) used do not return a value, those rows are hidden. The measure that uses the detail table values will only return a value for the budget/item combinations that exist within it. The measure on the Budget table only uses Budget as a filter, not Item, so it will return a value for every Budget and that value will apply to every item.
 
Upvote 0
Thanks Rory,

I was just about to post exactly the same table as you have above, and you've answered my question. In fact it also explains why 'shows items with nothing on rows' also works. Not sure what setup I had previously, but it was a while ago and I convinced myself it would work. Seems it won't.

For info I had got the previous system working with many-to-many relationships and cross table filtering through an intermediate table. I would describe my efforts as being those of a gifted amateur, with significant holes in my knowledge!!

Thanks again.
 
Upvote 0

Forum statistics

Threads
1,226,452
Messages
6,191,132
Members
453,641
Latest member
enfkkviesm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top