View Full Version : Rearranging overhead costs in financial statement using power query - possible?

2017-03-14, 03:31 PM
I face a challenge with regrouping costsand recalculating totals in our financial statements.
It relates to our overheadcosts, “Common Costs” which from central departments has been split to eachProfit Center.
I wish to undo this action and handle the Common Costs just like our Profit Center.
My issue is that the totals in the dataset are pre-calculated and therefore my effort in moving the costs to a Profit Center (succeeded, see below) is not sufficient, as my total (EBIT) does not change when choosing single Profit Centers different from the new Common Costs Profit Center.

Making our Common Costs appear as a ProfitCenter is straight forward in Power Query using simple If/else:
if [AccountRetail]="Common costs" then [AccountRetail] else [Profit Center]

And for a new Profit Center ID, I did thefollowing:
if [AccountRetail]="Common costs"then [Chain]&[Country] else [MEMBER KEY]

But I’m not there yet.
The Column “AccountRetail” which shows thehierarchy of my financial statement contains pre-calculated subtotals likeEBIT. When I choose not to show my newly created Profit Center (Common Costs),I still get the same EBIT shown, since the subtotal is pre-calculated andrelated to all the old Profit Centers.

Any ideas of how to cope with this?
Any better way of handling this rearrangement of costs and change pre-calculatedtotals like EBIT in the data?

My dataset is a column of headlines for the financial statement and a column of values related.

I appreciate any comments J

Ken Puls
2017-03-16, 05:31 PM
Hey there,

Can you upload a small (faked) data set that shows the inputs and desired output? I think that might make it a bit easier to work through.

2017-03-27, 02:46 PM
Hi Ken,
I have this example as shown in the attached picture.
The yellow marked areas are the common costs that has to change
The orange and blue markings is the pre-calculated totals that has to change when moving common costs away from the shop results.

Hope this explains carefully enough...?