Multi-year Budgets vs. Allocations Power Pivot Table (from 2 different Excel tables)

[Adam_Algar]

Hello all,

Long time excel user, first time poster. :) 

I have attached example tables to facilitate an understanding of what I am trying to do. I am rather new to Power Pivot, Power Query (Get&Transform) and all of the wonderful features, but I am very familiar with Excel in general, and feel very comfortable learning new things. I have been successful in creating power pivot tables and using power query features, and have created bridging tables of unique entries in my own data models to help combine information to within single tables. 

On to the problem: I have two separate Excel tables, one tracking available Budgets year over year, and the other tracking allocations of funding to recipients, traceable back to the Budget reference titles (read: "Budget source" column in each table). The tables need to remain separate, and years need to remain as columns, not rows. However, there is a great deal of flexibility otherwise. 

Overall, I am trying to create a Power Pivot table that will easily update itself and show year-over-year budget amounts per budget, total allocations from said budget, and the variance (surplus/unspent or deficit). 

I have added both tables to the data model, and a relationship can be drawn using "Budget source" column. However, I cannot seem to create a power pivot table that will show each budget's available amounts in one row, followed immediately by allocations from said budget in the second row, and then a variance equation automatically calculating whether there is any remaining money (surplus/unspent) or if it is oversubscribed (deficit). 

I hope the attachment provides sufficient information. I have explained perhaps better in the notes boxes under each table what I am trying to do. I am sure this is doable, I am just not advanced enough in my understanding. 

Other info:

• Device and OS platform: PC, Windows 7 Enterprise
• Excel product name and version number: MS Excel Professional Plus 2016 (16.0.5149.1000), 32bit.

Thank you ahead of time for your help and perspectives. Cheers!

Adam

[#2]

I think the only way to do that is not through power pivot because the power in the power pivot is in creating relationships (one to many). With many to many your only recourse is to unpivot both tables in power query then merge append both tables then add a designation column to id which data is a budget data and which data is the allocation data to differentiate them, then create a normal pivot table to summarize them. the budget id will go in column as well as the designation (i.e Budget, Allocation, and the total between the two will be the variance have to make the allocation a negative number though

[#3]

@Adam_Algar 

As variant:

- query both tables

- remove rows with Totals

reference them, and for each

- not to lost years without data replace null on 0, since number of columns is flexible use Table.ColumnNames() as list of columns to replace

- unpivot other than budget columns

- remove records which contains "total" (removing former rows totals)

- change back 0 on null

generate list of years in case if it is different in both tables

- reference again initial tables

- remove budget columns

- get list of column names and convert to table

- combine both queries with years and remove duplicates

Load unpivoted budgets and years into data model

create relationships as

add DAX measures as

Allocated Budget:=SUM(Allocations[Allocated])

Available Budget:=CALCULATE (
   SUM(AvailableBudgets[Available]),
   TREATAS(VALUES(Allocations[Budget source]),AvailableBudgets[Budget source])
   )

Remaining Budget:=[Available Budget]-[Allocated Budget]

apply proper formatting to measures

Build PivotTable from data model

it is in the second sheet attached.

I'm not sure how it works on Excel 2016 on Win7, both are outdated with limited or no support, at least without features update. But you may try.

[#4]

Thanks very much for this detailed response. I have tried it, and while I am sure it would work in newer / updated versions, it sadly seems to fall apart at the "treatas" DAX part, meaning it doesn't exist in my version. Not likely to be able to resolve this version problem any time soon unfortunately.

Great response though, thanks again!

[#5]

Thanks, I will try this !

[#6]

@Adam_Algar 

If TREATAS is not supported by your version you may use some equivalent

Available Budget Variant:=CALCULATE (
   SUM(AvailableBudgets[Available]),
   FILTER( ALL(AvailableBudgets[Budget source]),
           CONTAINS(
              VALUES(Allocations[Budget source]),
              Allocations[Budget source],
              AvailableBudgets[Budget source]))
   )

It returns the same result

CONTAINS shall be available, that's quite old function.