You’re offline. This is a read only version of the page.
5
Hi!
I am trying to create a relationship/comparison between budgeted and actual amounts in Excel/Power Pivot with Odata from Budget (Page 120 G/L Budget Entries) and General ledger (Page 20 General Ledger Entries) for G/L Account No, Global dimension 1 and Date. However, when importing the above pages in Power Pivot I don't know how to create the relationship for Budget vs Actual for a specific account no, global dimension and date, as in the picture below - General Ledger and Budget appear as separate "sources" when i'm creating the Pivot Table and thus they cannot be compared
I have also done an import of page DimensionSetEntries and Chart of Accounts. Do any of you know how I can create a relationship and compare Actual Amount with Budgeted amount from the above Odata-pages in PowerPivot?
I am also interested in this topic.
I am using Power BI , and have gathered Data using Power Query from Dynamics 365 Business Central.
I have tables:
Accounts, General Ledger Entries, Budget Entries, Dimensions (Cost centers), DimTable
But also unsure how to relate, i did not find anything.
One option / direction is to create a new Dax table with comparison, like:
Share
Report
All responses (
Answers (
ActualsVsBudget = FILTER(
CROSSJOIN(
SELECTCOLUMNS('G/L Entries',
"Account", 'G/L Entries'[Bal Account No],
"Posting Date", 'G/L Entries'[Posting Date],
"ActualAmount", -1*'G/L Entries'[Amount],
"ActualCostCenter", 'G/L Entries'[Global Dimension 1 Code],
"Actual Description", 'G/L Entries'[Description]
),
SELECTCOLUMNS('G/L Budget Entries',
"Budget Account", 'G/L Budget Entries'[G/L Account No],
"Budget Date", 'G/L Budget Entries'[Date],
"BudgetAmount", 'G/L Budget Entries'[BudgetAmount],
"BudgetCostCenter", 'G/L Budget Entries'[Global Dimension 1 Code],
"Budget Description", 'G/L Budget Entries'[Description]
)
),
[Account] = [Budget Account] &&
([ActualCostCenter] = [BudgetCostCenter] || [BudgetCostCenter] = "" || [ActualCostCenter] = "")
)
