Translating Sheet Formulas into DAX

@mspanic and any other helpful individual.

1. Please assist. I have converted formulas from a spreadsheet(ttps://docs.google.com/spreadsheets/d/1ubo9TAz2zzCpKbfO5SFBMjFLgjFtUxhY/edit?usp=sharing&ouid=104129043494164133703&rtpof=true&sd=true ) into DAX. I just need a person to please go over the steps and assure me that my conversions/calculations are correct.

2. The Dax Calculation is for " Cash flow imbalance".

2.1 Click in column K and you will see the formula. You need to go to each cell that is referenced to get the complete calculation and so on.

My Dax for the above row:

ash flow imbalance =
[Cash flow from ops - management]

• [Cash flow from ops - statutory]
• CALCULATE(
SUM(‘ZTBR’[Amount in USD]),
‘ZTBR’[Roll_Up_Function] = “B allocations not in mgt cash flow”
)
2.2 Dax calculations are under Metric calculations

The Main code

Totals by Account Description & Code5 =
VAR SelectedCashFlowDescription = SELECTEDVALUE(‘CashflowDiscriptions’[Description], “None”)
VAR _TotalNetDept = CALCULATE([Net debt increase/decrease], ALL(CashflowDiscriptions))
VAR _TotalNetTaxPaid = CALCULATE([Net Tax Paid], ALL(CashflowDiscriptions))
VAR _TotalIntercoCashFlows = CALCULATE([IntercoCashFlows], ALL(CashflowDiscriptions))
VAR _TotalOtherCashFlowItems = CALCULATE([Other cash flow items], ALL(CashflowDiscriptions))
VAR _TotalInterestPaid = CALCULATE([Interest paid], ALL(CashflowDiscriptions))
VAR _TotalInterestExpenseNotImpactingCashFlow = CALCULATE([Interest expense not impacting cash flow], ALL(CashflowDiscriptions))
VAR _TotalCashFlowFromOpsStatutory = CALCULATE([Cash flow from ops - statutory], ALL(CashflowDiscriptions))
VAR _TotalDisposalsImpairmentofFixedAssets = CALCULATE([Disposals & impairment of fixed assets], ALL(CashflowDiscriptions))
VAR _TotalWorkingCapitalMvtsExclProvisions = CALCULATE([Working capital mvts excl. provisions], ALL(CashflowDiscriptions))
VAR _TotalOtherPPEAcquired = CALCULATE([Other PP&E acquired], ALL(CashflowDiscriptions))
VAR _TotalPurchasesOfPPE = CALCULATE([Purchases of PP&E], ALL(CashflowDiscriptions))
VAR _TotalWorkingCapitalMvtsInclProvisions = CALCULATE([WorkingCapitalMvtsInclProvisions], ALL(CashflowDiscriptions))
VAR _TotalProceedsOtherPPEDisposals = CALCULATE([Proceeds other PP&E disposals], ALL(CashflowDiscriptions))
VAR _TotalProceedsPoolingDisposals = CALCULATE([Proceeds pooling disposals], ALL(CashflowDiscriptions))
VAR _TotalProceedsFromDisposals = CALCULATE([Proceeds from disposals], ALL(CashflowDiscriptions))
VAR _TotalPoolingEquipmentAcquired = CALCULATE([Pooling equipment acquired], ALL(CashflowDiscriptions))
VAR _TotalCapitalExpenditure = CALCULATE([Capital expenditure], ALL(CashflowDiscriptions))
VAR _TotalWorkingCapitalBudgetFlex = CALCULATE([WorkingCapitalMvtsInclProvisions], ALL(CashflowDiscriptions))
VAR _TotalCashFlowFromOpsManagement = CALCULATE([Cash flow from ops - management], ALL(CashflowDiscriptions))
VAR _TotalCashFlowImbalance = CALCULATE([Cash flow imbalance], ALL(CashflowDiscriptions))
VAR _LeasesPrincipalRepaid = CALCULATE(SUM(Bracs[Metric Value (\$)]), Bracs[Metric] = “Leases principal repaid”)

RETURN
SWITCH(
TRUE(),
SelectedCashFlowDescription = “Interco capital returned”, 678,
SelectedCashFlowDescription = “Net debt increase/(decrease)”, _TotalNetDept,
SelectedCashFlowDescription = “Cash flow imbalance”, _TotalCashFlowImbalance,
SelectedCashFlowDescription = “Cash flow from ops - management”,_TotalCashFlowFromOpsManagement,
SelectedCashFlowDescription = “Working capital mvts incl. provisions”, _TotalWorkingCapitalBudgetFlex,
SelectedCashFlowDescription = "Proceeds from disposals ", _TotalProceedsFromDisposals,
SelectedCashFlowDescription = “Proceeds other PP&E disposals”, _TotalProceedsOtherPPEDisposals,
SelectedCashFlowDescription = “Proceeds pooling disposals”, _TotalProceedsPoolingDisposals,
SelectedCashFlowDescription = “Capital expenditure”, _TotalCapitalExpenditure,
SelectedCashFlowDescription = “Net tax paid”, _TotalNetTaxPaid,
SelectedCashFlowDescription = “IntercoCashFlows”, _TotalIntercoCashFlows,
SelectedCashFlowDescription = “Leases principal repaid”, _LeasesPrincipalRepaid,
SelectedCashFlowDescription = “OtherCashFlowItems”, _TotalOtherCashFlowItems,
SelectedCashFlowDescription = “Interestpaid”, _TotalInterestPaid,
SelectedCashFlowDescription = “Interest expense not impacting cash flow”, _TotalInterestExpenseNotImpactingCashFlow,
SelectedCashFlowDescription = “CashFlowFromOps - Statutory”, _TotalCashFlowFromOpsStatutory,
SelectedCashFlowDescription = “Disposals & impairment of fixed assets”, _TotalDisposalsImpairmentofFixedAssets,
SelectedCashFlowDescription = “Working capital mvts excl. provisions”, _TotalWorkingCapitalMvtsExclProvisions,
SelectedCashFlowDescription = “Working capital mvts incl. provisions”, _TotalWorkingCapitalMvtsInclProvisions,
SelectedCashFlowDescription = “Pooling equipment acquired”, _TotalPoolingEquipmentAcquired,
SelectedCashFlowDescription = “Other PP&E acquired”, _TotalOtherPPEAcquired,
SelectedCashFlowDescription = “Purchases of PP&E”, _TotalPurchasesOfPPE,
SelectedCashFlowDescription = “Working capital mvts incl. provisions”, _TotalWorkingCapitalBudgetFlex,

``````    // Default calculation if no specific match is found
CALCULATE(
SUM('ZTBR'[Amount in USD]),
'ZTBR'[Roll_Up_Function] IN {
"Cash share issues by B Limited",
"FX on foreign currency denominated debt",
"Cost sharing payments to B Limited",
"Cash flow from ops - management",
"Change in capex creditors",
"Profit before B allocations Total",
"Depreciation and amortisation",
"IPEP expense",
"Disposals & impairment of fixed assets",
"Profit on disposal of pooling equipment",
"Scrapped pooling equipment",
"Impairment or valuation adjustment of pooling equipment",
"Disposals or valuation adjustments of other fixed assets",
"Other current debtors",
"Share-based payments expense",
"Working capital mvts excl. provisions",
"Debtor movements",
"Creditor movements",
"Inventory movements",
"Prepayment movements",
"Change in loss compensation balances",
"Provision movements",
"FX on foreign currency denominated debt Total",
"B allocations not in mgt cash flow",
"Interco interest and guarantee fees",
"Interco cash flows",
"Interco royalties",
"Internal restructuring",
"Interco dividends Total",
"Change in interco balances",
"Change in interco recharge clearing",
"FX on interco debt",
"Interco capital returned",
"Interest expense Total",
"Interest revenue",
"Interest paid",
"Cash flow from ops - statutory adjustment",
"Lease interest",
"Change in loss compensation balances",
"Statutory reallocations",
"Change in deferred fixed asset proceeds",
"Proceeds disposals of intangible assets",
"Proceeds software intangibles disposals",
"WDV software intangibles disposals",
"Profit software intangibles disposals",
"Proceeds other intangibles disposals",
"WDV other intangibles disposals",
"Profit other intangibles disposals",
"Proceeds deferred expenditure disposals",
"WDV deferred expenditure disposals",
"Profit deferred expenditure disposals",
"Proceeds investment disposals",
"WDV investments disposals",
"Profit investments disposals",
"B allocations not in mgt cash flow",
"Cash flow from ops - statutory adjustment"
}
) + CALCULATE(
SUM(Bracs[Metric Value (\$)]),
Bracs[Metric] IN {
"Entity acquisitions and disposals",
``````

“Entity acquisitions”,
“Total purchase price of entities acquired”,
“Purchase price of entities acquired”,
“Cash price of entities acquired”,
“Non-cash consideration in purchase price”,
“Costs directly incurred on entity acquisitions”,
“Deferred settlement of entity acquisitions”,
“Deferred settlements now paid”,
“Non-cash consideration for entity acquisitions”,
“Net cash held by entity at date of acquisition”,
“Proceeds from entity disposals”,
“Net price of entities disposed”,
“Agreed sale price of entities disposed”,
“Cash price of entities disposed”,
“Non-cash consideration in agreed sale price”,
“Costs directly incurred on entity disposals”,
“Provisions raised on entity disposals”,
“Net change disposal provisions and accruals”,
“Deferred settlement of entity disposals”,
“Non-cash consideration for entity disposals”,
“Net cash held by entity at date of disposal”,
“Entity disposal cash flow tax adjustments”,
“Entity disposal cash flow other adjustments”,
“Working capital - budget flex”,
“Pooling equipment replacements”,
“Pooling equipment internal transfers”,
“Other PP&E replacements”,
“Other PP&E internal transfers”,
“Joint venture loans”,
“WDV pooling equip. disposals & write-offs”,
“Gain pooling equip. disposals & write-offs”,
“WDV other PP&E disposals”,
“Profit other PP&E disposals”,
“Discount unwind on long term provisions”,
“Tax paid”,
“Joint venture loans”,
“Tax refunded”,
“Working capital - budget flex”,
“Fiscal unity tax transfers”,
“Other cash flow items”,
“Change in cash net of overdraft”
}
)
)

Hi @Yrstruly ,

It little bit exceed the forum topic

• you can try to go to :

to show all formula

If it is needed you can copy- paste, switch columns - from value to line description

and then compare to your DAX.

• maybe you can try some of Enterprise DNA Data Mentor AI Tools

Good luck.

Viewing the formulas are not the problem. It is interpreting them to get to the correct amount for the row in question.