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

%3CLINGO-SUB%20id%3D%22lingo-sub-2299580%22%20slang%3D%22en-US%22%3EMulti-year%20Budgets%20vs.%20Allocations%20Power%20Pivot%20Table%20(from%202%20different%20Excel%20tables)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2299580%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELong%20time%20excel%20user%2C%20first%20time%20poster.%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20example%20tables%20to%20facilitate%20an%20understanding%20of%20what%20I%20am%20trying%20to%20do.%20I%20am%20rather%20new%20to%20Power%20Pivot%2C%20Power%20Query%20(Get%26amp%3BTransform)%20and%20all%20of%20the%20wonderful%20features%2C%20but%20I%20am%20very%20familiar%20with%20Excel%20in%20general%2C%20and%20feel%20very%20comfortable%20learning%20new%20things.%20I%20have%20been%20successful%20in%20creating%20power%20pivot%20tables%20and%20using%20power%20query%20features%2C%20and%20have%20created%20bridging%20tables%20of%20unique%20entries%20in%20my%20own%20data%20models%20to%20help%20combine%20information%20to%20within%20single%20tables.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20to%20the%20problem%3A%20I%20have%20two%20separate%20Excel%20tables%2C%20one%20tracking%20available%20Budgets%20year%20over%20year%2C%20and%20the%20other%20tracking%20allocations%20of%20funding%20to%20recipients%2C%20traceable%20back%20to%20the%20Budget%20reference%20titles%20(read%3A%20%22Budget%20source%22%20column%20in%20each%20table).%20The%20tables%20need%20to%20remain%20separate%2C%20and%20years%20need%20to%20remain%20as%20columns%2C%20not%20rows.%20However%2C%20there%20is%20a%20great%20deal%20of%20flexibility%20otherwise.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOverall%2C%20I%20am%20trying%20to%20create%20a%20Power%20Pivot%20table%20that%20will%20easily%20update%20itself%20and%20show%20year-over-year%20budget%20amounts%20per%20budget%2C%20total%20allocations%20from%20said%20budget%2C%20and%20the%20variance%20(surplus%2Funspent%20or%20deficit).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20added%20both%20tables%20to%20the%20data%20model%2C%20and%20a%20relationship%20can%20be%20drawn%20using%20%22Budget%20source%22%20column.%20However%2C%20I%20cannot%20seem%20to%20create%20a%20power%20pivot%20table%20that%20will%20show%20each%20budget's%20available%20amounts%20in%20one%20row%2C%20followed%20immediately%20by%20allocations%20from%20said%20budget%20in%20the%20second%20row%2C%20and%20then%20a%20variance%20equation%20automatically%20calculating%20whether%20there%20is%20any%20remaining%20money%20(surplus%2Funspent)%20or%20if%20it%20is%20oversubscribed%20(deficit).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20the%20attachment%20provides%20sufficient%20information.%20I%20have%20explained%20perhaps%20better%20in%20the%20notes%20boxes%20under%20each%20table%20what%20I%20am%20trying%20to%20do.%20I%20am%20sure%20this%20is%20doable%2C%20I%20am%20just%20not%20advanced%20enough%20in%20my%20understanding.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EOther%20info%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CUL%3E%3CLI%3EDevice%20and%20OS%20platform%3A%20PC%2C%20Windows%207%20Enterprise%3C%2FLI%3E%3CLI%3EExcel%20product%20name%20and%20version%20number%3A%20MS%20Excel%20Professional%20Plus%202016%20(16.0.5149.1000)%2C%2032bit.%3C%2FLI%3E%3C%2FUL%3E%3CP%3EThank%20you%20ahead%20of%20time%20for%20your%20help%20and%20perspectives.%20Cheers!%3C%2FP%3E%3CP%3EAdam%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2299580%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

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

 

 

 

0 Replies