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.
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.
Trustee Board - Financial Summary v7.xlsx | |||||||||
---|---|---|---|---|---|---|---|---|---|
I | J | K | L | M | N | O | |||
17 | tblBudget | tblDetail | |||||||
18 | Category | Budget | Category | Description | Spend | ||||
19 | Budget1 | 100 | Budget1 | Item1 | 100 | ||||
20 | Budget2 | 20 | Budget1 | Item2 | 200 | ||||
21 | Budget3 | 300 | Budget1 | Item3 | 300 | ||||
22 | Budget3 | Item4 | 400 | ||||||
23 | Budget3 | Item5 | 500 | ||||||
24 | |||||||||
25 | Pivot Table | ||||||||
26 | Row Labels | Test Budget Amount | Test Detail Spend | ||||||
27 | Budget1 | ||||||||
28 | Item1 | £100.00 | £100.00 | ||||||
29 | Item2 | £100.00 | £200.00 | ||||||
30 | Item3 | £100.00 | £300.00 | ||||||
31 | Item4 | £100.00 | |||||||
32 | Item5 | £100.00 | |||||||
33 | Budget2 | ||||||||
34 | Item1 | £20.00 | |||||||
35 | Item2 | £20.00 | |||||||
36 | Item3 | £20.00 | |||||||
37 | Item4 | £20.00 | |||||||
38 | Item5 | £20.00 | |||||||
39 | Budget3 | ||||||||
40 | Item1 | £300.00 | |||||||
41 | Item2 | £300.00 | |||||||
42 | Item3 | £300.00 | |||||||
43 | Item4 | £300.00 | £400.00 | ||||||
44 | Item5 | £300.00 | £500.00 | ||||||
45 | Grand Total | £420.00 | £1,500.00 | ||||||
Summary |