Power Pivot: Allocation of values

[eod122]

Hello,

first post on this forum so I hope I’m not making too much errors here... 

I’m struggling with an seemingly easy task which I was able to solve in regular Excel (but in a very inflexible way) and hope that Power Pivot might solve this. Haven't found an solution by browsing thorugh other posts on this Forum so far.

I got
- A list of transactions per cost center and account which is regurarly updated
- A list of cost centers. Additional and relevant information here is that each cost center is split into various spheres (for tax reasons in case you wonder). Total is always 1 (or 100%) per cost center, the amount can be split between up to 10 spheres.

Outcome should be a Pivot table where I can drill down to accounts, cost centers, spheres…

The list from my current (non-power pivot based) solution looks like this and is easy to use in a pivot table,
but is way too long (Number of rows = All Accounts X All Cost centers X All Spheres X All Reporting periods - currently more than 100k rows) and takes too much time to update once a dimension changes. Amount (Total) is directly retrieved from an ERP System and then multiplied with "Sphere %" to get "Amount (Sphere)"

CostCenter&Sphere CostCenter Sphere Sphere% Account Amount(Total) Amount(Sphere)
1410BB 1410 BB 25% 621100 32,64 8,16
1410WE 1410 WE 75% 621100 32,64 24,48
1420BB 1420 BB 25% 621100 49,74 12,43
1420WE 1420 WE 75% 621100 49,74 37,30
1440PI 1440 PI 50% 621100 7,94 3,97
1440WE 1440 WE 50% 621100 7,94 3,97


In Power Pivot I only manged to create calculated columns by connecting both tables via the cost center and adding columns with formulas like =[Amount]*related(CostCenter[IB]).
However this creates only additional columns. I like to ultimately only have one measure (such as „Amount (Sphere)“ in the exisiting list).

Thanks for your help and ideas!


Appendix:

List of Transactions

CostCenter Account Amount
1410 621100 32,64
1410 702000 5,11
1420 621100 49,74
1420 702000 36,23
1430 621100 92,17


Cost Centers & Spheres (IB, PI, WE, etc.)

CostCenter IB PI WE BB
1410 0% 0% 75% 25%
1420 0% 0% 75% 25%
1430 0% 0% 75% 25%
1440 0% 50% 50% 0%
1450 100% 0% 0% 0%

 

[Matt Allington]

Welcome to the great new world of power pivot. It is very learnable, but you indeed need to learn. It is not the same as Excel. Here are 3 posts I created to help people get started - you should read them both
The Optimal Shape for Power Pivot Data - Excelerator BI
Calculated Columns vs Measures in DAX - Excelerator BI
Multiple Data Tables in Power Pivot - Excelerator BI


you would also benefit by reading my book Learn to Write DAX - Excelerator BI

Your tables are data tables. You need to create a lookup table for Cost centre and Account and join both data tables to both lookup tables. The. Split the problem into pieces and write measures to meet your needs.