Hello All
My first post - usually, when I have a problem, I keep searching the Internet until I can figure the answer. This time I've come to a dead end and need help
As background I've been using spreadsheets since before Excel came on the scene and am comfortable, though by no means an expert, with data normalisation. However I've only recently started using Power Pivot
I've created a PP data model to represent the costs involved in a project, with a basic structure as follows:
The lowest level is the Cost Item, held in tblCostItems. Each Cost Item belongs to a SubActivity, held in tblSubActivities.
[tblCostItems]SubAct is linked to [tblSubActivities]SubActID
Each SubActivity belongs to both an Activity and to a Partner. There are higher level links that are not relevant to this problem.
[tblSubActivities]Activity is linked to [tblActivities]ActivityID
[tblSubActivities]Partner is linked to [tblPartners]PartnerID
For each Cost Item there's a Current Budget. In pivot tables, Current Budget sums perfectly according to SubActivity, Activity and Partner.
There's a separate table that lists expenditure. Ideally, Expenditure would be recorded against Cost Item, but this is not possible. Instead, Expenditure is recorded against SubActivity. For each record, the project month (an integer from 1 - 36) in which the expenditure occurred, is recorded in field ExpMonth
[tblExpenditure]ExpSubAct links to [tblSubActivities]SubActID
[tblExpenditure]ExpMonth links to [tblMonths]Month
The pivot table showing Expenditure by Month (SubActivities as rows, ExpMonth as columns) works fine. What I want to do is add a final column to the Expenditure by Month PT, which shows the Current Budget for the SubActivity. Something like this:
SubActivity1 Month1Exp.1 Month2Exp.1 .... Month36Exp.1 GrandTotalExp1 CurrentBudget1
SubActivity2 Month1Exp.2 Month2Exp.2 .... Month36Exp.2 GrandTotalExp2 CurrentBudget2
...so we can see expenditure by month, total expenditure to date and Current Budget for each activity. But I can't make this happen. The PT shows me the correct monthly expenditure phasing, but Current Budget is repeated 36 times. I can (sort of) understand why it's happening, but just can't figure a way around it.
Of course I can hide the unwanted columns, but that's not tackling the underlying problem (and the OCD side of me would NOT be happy!)
The complete workbook is too large to attach, but can be accessed here. My attempts at building the PT are on sheets PT1 and PT2.
Any advice greatly appreciated.
Dave
Hi David,
You can find the answer here: https://powerpivotpro.com/2013/05/relationship-may-be-needed-but-i-already-have-a-relationship-whats-going-on/
Noticed that you are not using a date table, basically any data model that needs time intelligence calculations should have a calendar table.
Catalin
Hi Catalin
Mulțumesc frumos pentru răspuns (that's my Romanian exhausted!)
I've spent hours following the advice in the post that you suggested, but am getting nowhere. Whatever I do, the CurrentBudget column appears alongside the Expenditure column for every month that there's expenditure, but I only want to see the budget once. Is this inevitable given that I'm using Month as a column heading? I know I could write a simple VBA routine to hide these unwanted columns, but would much prefer to prevent them from appearing in the first place.
As or the calendar table, I appreciate that these are usually a necessary part of a data model, but I've no need for time intelligence calculations - in essence my tblMonths (I think) serves as a calendar table to the level of detail that I need.
Thanks again.
David
I've only got Excel 2010 here so can't actually work with your model, but as a simple solution, couldn't you just create a set based on column items and use that to hide the unwanted columns?
Hi Velouria
Thanks for the tip. Actually I'd managed to figure that one out for myself over the weekend. However then I noticed that, even after I'd manged to get the columns I wanted, the numbers they displayed were incorrect
, so back to the drawing board...
Dave
Hi David,
If you add a new Column named CurrBudget in tblExpenditure, with this formula: =RELATED(tblSubActivities[SumCuurentBudget]),
All you have to do in PT2 pivot table is to remove the existing column SumCurrentBudget (which comes from tblSubActivities table) from Values section and add the new column currBudget in the values section of the pivot. You should see the pivot like in image attached, I guess this is the way you wanted to see the data.
Hi Catalin
I'm afraid that the image still shows the same problem - the CurrentBudget is shown for every month. From a business point to view this doesn't make sense, as the budget for each activity has no phasing - it's simply a particular amount for the activity. I know the columns can be "hidden" by creating a Set, but is there a way of designing the PT such that these cloumns are never created in the first place, or are they inevitable because I'm using Month in COLUMNS?
Thanks again.
David
Hi David,
Yes, they're inevitable because you have the Month column in the column labels.
Mynda
Thanks Mynda - now I won't waste any more time trying to resolve this "error".
Regards
David
You could try creating a Named Set as Velouria suggested.
Hi Mynda
I will try doing that. What I meant was that I'll stop trying to find a method that doesn't generate the columns in the first place.
Thanks again.
Regards
David
Hi David,
Maybe I'm missing something, but have you noticed that in the image there are only 2 months displayed, not all 36 as you had before? Isn't that what you wanted? And the numbers are not the same on those 2 months displayed, as you had originally, there is nothing that you can hide.
Here is a link to the updated file, note that it is a temporary link: https://1drv.ms/x/s!AjfS33R8yoG9jvAKVqc9G2UAr87nQw
Hi Catalin
There's only two months showing because I only have expenditure data for two months! However it's clear that the budget column will be repeated for every month for which I have expenditure. I can live with the act that I could hide the unwanted columns through creating a set. However I've now noticed another problem. The budget for a SubActivity is repeated for expenditure row under that SubActivity. If there are n expenditure records then CurrentBudget for the SubActivity is n times what it should be. No doubt there's a way around this with some clever DAX, but it's way beyond my ability!
Thanks for the link - I've downloaded the file.
David
