Log In
Register
Home
July 20, 2019
Offline
Dear All,
I am trying to build an accounting package for use on a ship. A budget has been set for various sections/sub-sections of expenditure. The heads of department are responsible for listed sections of expenditure.
The reporting pivot needs to show both the budget monthly and the expenditure by department. The complication comes because when the Captain is selected I want him to be presented with data from all sections, not just those for which he is individually responsible. I have almost managed to achieve this. However, as you can see in the attached spreadsheet the budget is only assessed when an expenditure occurs, not every month as I require. At the moment I make use of the cross table filtering (as described by others elsewhere) by including 'tblSpend' as one criteria in the Calculate function of the Budget Measure. I could remove that, but then Budget reports on every sub-section regardless of which department is selected - this makes for a very long pivottable with much incorrect/irrelevant data.
I've thought to use CrossFilter to stop some of the filtering on dates, but there is no actual relationship between the 'tblSpend' and 'tblBudget', only the magic of cross table filtering makes it work as it does. I've also tried various versions of ALL(...), but also without success.
Perhaps I'm on completely the wrong track - but it seems to almost work!
I've included the spreadsheet which shows the result I'm getting and the result I want plus all the anonymised data; i've also included a picture of the data model as this might help.
Any suggestions gratefully received.
See Answer
July 16, 2010
Offline
Hi Peter,
Thanks for sharing your file and very clear illustration of what you're trying to achieve.
The solution is easy. Add a date column to your budget table that contains a date for each month, then spread or assign a budget amount to each date. This can be one date for each month if you only want to summarise it at the month level or higher.
Then create a relationship between the budget table and date table.
Note: your 'required PivotTable' appears to show the budget as a running total and the Spend on a monthly total. I think they should both be on the same basis e.g. monthly totals, otherwise you are not comparing like for like.
I hope that points you in the right direction.
Mynda
Answers Post
July 20, 2019
Offline
Hi Mynda,
Thanks for the quick reply - I'll investigate using Powerquery to add the dates as the users would be confused if I included it on the budget entry sheet. I had thought about doing something like this, but even at only the month level in the real workbook this will result in a table of over 2000 rows and over 70000 if its for a year. I hoped there might be a more elegant solution; I suspect there will be a performance issue when refreshing a table of that size.
With regard to the cumulative nature of the 'Spend' in the real workbook I use YTD to do just that, but didn't do it in this example.
I'll let you know how I get on.
Many thanks.
Peter
July 16, 2010
Offline
Hi Peter,
70k rows is nothing for Power Pivot. I doubt it will even blink. Just make sure if you use formulas to apportion the budget over months that you paste it all as values rather than leave active formulas in the budget table.
Alternatively, you could use Power Query to do the apportionment, in which case it would be even more efficient because the data in the worksheet will only be the size it currently is and the 70k rows will be in Power Pivot, which is super efficient at compressing data.
Mynda
July 20, 2019
Offline
Hi Mynda,
Firstly apologies for delay in replying, we've have internet/phone problems.
I've used PQ to create the qryDailyBudget which has an entry for every Section/Sub-Section and apportioned the budget on a daily basis. As you say, no appreciable delay.
However, I'm still stuck. I did allude to the problem in my original query and that is when expanding the Sections into Sub-Sections the budget data becomes confused. I've discovered I have a choice:
1. The budget can be correctly apportioned across the months/years, but whenever the data is expanded to show Sub-Sections the pivot table now shows every sub-section against each Section. This is shown in the attached image: 'With working budget...'. This occurs when the measure definition is [Budget]=CALCULATE(sum(qryDailyBudget[Daily]), tblDepSect ).
2. I can correct this by including tblSpend as a cross table filter when defining the measure [Budget]=CALCULATE(sum(qryDailyBudget[Daily]), tblDepSect, tblSpend ). However, I'm now back where I was with the Budget only calculated for the dates on which an expenditure occurred. This is shown in the image 'With Broken Budget....'
I think the issue is something to do with the fact that when including the field tblSpend[Sub-Section] in the pivot it has no meaning for the qryDailyBudget, this is cured when tblSpend is used as a cross table filter, but it applies to every column in qryDailyBudget. Including the dates; I want it to apply only to the Sub-Section column, but I don't know how to make that work.
I've included the updated spreadsheet and also the PowerPivot diagram view.
Any ideas gratefully received.
July 20, 2019
Offline
Hi Mynda,
Finally fixed it. I had to extend the M2M concept to manage also at the Sub-Section level. This resulted in a more complex Diagram view (copy attached), but produced the result I wanted, which is in the attached spreadsheet. I can't help thinking that I've somehow got my data structured incorrectly, but at least this produces the required result.
For info, I've also gone back to using the Calendar as a Disconnected table and extracting StDate and EndDate as measures (min and max values) and calculating the budget values that way. The advantage is that I don't have to create any PQ data (accepting that that overhead is minimal) but I also don't have to worry what date range to consider - the two measures handle that detail.
Hope this helps someone else.
Many thanks.
Peter
July 16, 2010
Offline
Thanks for sharing, Peter.
Your approach is unique and works fine for one year of data, which might be all you need it for. Keep in mind that if you want to add next year's data to it or use any of the Time Intelligence DAX functions that allow for year on year comparisons etc., then this structure won't work. Hence my original advice to split the budget out over the months.
Obviously I don't know if this will ever be a requirement for you, so I've only mentioned this so that if others want to use this approach they know the limitations.
Mynda
July 20, 2019
Offline
Thanks Mynda,
I have never yet had occasion to really use them. However, I've just had a quick look at a blog (on https://exceleratorbi.com.au/) about them and clearly have much to learn - I can think of a couple of occasions they might have saved me a pile of work!. Many thanks for the help.
All the best.
Peter
July 16, 2010
Offline
Yes, Power Pivot is a vast topic. If you're interested in getting your skills up to speed quickly, please consider my Power Pivot course.
1 Guest(s)
