Forum Discussion
Multi-year Budgets vs. Allocations Power Pivot Table (from 2 different Excel tables)
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.
- 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!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.
