Apr 27 2021 01:23 PM
Apr 27 2021 01:23 PM
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.
Thank you ahead of time for your help and perspectives. Cheers!
May 14 2021 09:20 PM
May 15 2021 03:09 AM
- 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.
May 19 2021 07:56 AM
May 19 2021 09:44 AM
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.