@mspanic
I am using the dax below to display figures from a table:
Blockquote
Measure =
VAR NegativeItems = {
“Profit before B allocations Total”,
“Share-based payments expense”,
“Debtor movements”,
“Creditor movements”,
“Inventory movements”,
“Prepayment movements”,
“Provision movements”,
“Change in capex creditors”,
“Change in loss compensation balances”,
“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”,
“Brambles allocations not in mgt cash flow”,
“Cash flow from ops - statutory adjustment”,
“Interest revenue”,
“Change in interest receivable”,
“Interest received cash flow adjustments”,
“Interest expense Total”,
“Amortisation of upfront fees”,
“FX on interco debt”,
“Finance expense capitalised”,
“Derivatives - FX forward revaluation”,
“Hedges of foreign currency interco debt”,
“Interest rate swaps FV adj. cash flow hedges”,
“Interest rate swaps FV hedges”,
“Bond fair value adjustment”,
“Change in interest payable”,
“Interest paid on other debt instruments”,
“Interest portion of financial instruments settled”,
“Interest paid cash flow adjustments”,
“Interco interest and guarantee fees”,
“Interco royalties”,
“Statutory reallocations”,
“Interco dividends Total”,
“Change in interco balances”,
“Change in interco recharge clearing”,
“Interco cash flow adjustments”
}
VAR PositiveItems = {
“Depreciation and amortisation”,
“IPEP expense”,
“Profit on disposal of pooling equipment”,
“Scrapped pooling equipment”,
“Impairment or valuation adjustment of pooling equipment”,
“Disposals or valuation adjustments of other fixed assets”,
“JV adjustment”,
“JV share of PAT”,
“JV dividends received”,
“Trading cash flow adjustments”,
“Other asset movements”,
“Dilapidation provision paid”,
“Lease adj - No longer used”,
“Pooling equipment internal tfr POSA adj”,
“Software intangibles acquired”,
“Other intangibles acquired”,
“Deferred expenditure”,
“Investments acquired”,
“Capital expenditure cash flow adjustments”,
“Capital expenditure - budget flex”,
“Change in deferred fixed asset proceeds”,
“Interest expense not impacting cash flow”,
“Lease interest”,
“FX on foreign currency denominated debt”,
“Interest cash flow not impacting expense”,
“Interest paid on leases”,
“Tax cash flow adjustments”,
“Free cash flow before dividends”,
“Dividends paid”,
“Dividends paid to Brambles shareholders”,
“Dividends paid to minorities”,
“Dividend cash flow adjustments”,
“Entity acquisitions and disposals”,
“Total purchase price of entities acquired”,
“Purchase price of entities acquired”,
“Cash price of entities acquired”,
“Costs directly incurred on entity acquisitions”,
“Deferred settlement of entity acquisitions”,
“Non-cash consideration for entity acquisitions”,
“Net cash held by entity at date of acquisition”,
“Entity acquisition cash flow adjustments”,
“Proceeds from entity disposals”,
“Net price of entities disposed”,
“Agreed sale price of entities disposed”,
“Cash price of entities disposed”,
“Costs directly incurred on entity disposals”,
“Provisions raised on entity disposals”,
“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”,
“Net equity raised by Brambles Limited”,
“Cash share issues by Brambles Limited”,
“Cash paid for share buyback”,
“Equity raised cash flow adjustment”,
“Internal restructuring”,
“FX on interco debt”,
“Interco capital issued”,
“Interco capital returned”,
“Cash balances transfer on merging entities”,
“Cashflow budget flex adjustment”,
“Financial instruments principal settled”,
“Loans drawn down”,
“Loan principal repaid”
}
VAR NegativeSum =
-SUMX(
FILTER(
‘ZTBR’,
‘ZTBR’[Classification] IN NegativeItems || ‘ZTBR’[Roll_Up_Function] IN NegativeItems
),
‘ZTBR’[Amount in USD]
)
VAR PositiveSum =
SUMX(
FILTER(
‘ZTBR’,
‘ZTBR’[Classification] IN PositiveItems || ‘ZTBR’[Roll_Up_Function] IN PositiveItems
),
‘ZTBR’[Amount in USD]
)
RETURN
NegativeSum + PositiveSum
Blockquote
I am calculating the Total of Interco cash flows which is made up of more than one row. I am using this calculatation to get total for Interco cash flows.
Blockquote
IntercoCashFlows =
VAR NegativeItems = {
“Interco interest and guarantee fees”,
“Interco royalties”,
“Statutory reallocations”,
“Interco dividends Total”,
“Change in interco balances”,
“Change in interco recharge clearing”,
“Interco cash flow adjustments”
}
VAR PositiveItems = {
“Internal restructuring”,
“FX on interco debt”,
“Interco capital issued”,
“Interco capital returned”,
“Cash balances transfer on merging entities”,
“Cashflow budget flex adjustment”
}
VAR NegativeSum =
-SUMX(
FILTER(
‘ZTBR’,
‘ZTBR’[Roll_Up_Function] IN NegativeItems || ‘ZTBR’[Classification] IN NegativeItems
),
‘ZTBR’[Amount in USD]
)
VAR PositiveSum =
SUMX(
FILTER(
‘ZTBR’,
‘ZTBR’[Roll_Up_Function] IN PositiveItems || ‘ZTBR’[Classification] IN PositiveItems
),
‘ZTBR’[Amount in USD]
)
RETURN
NegativeSum + PositiveSum
Blockquote
The rows displays value where it is avalailble:
Its not showing total for Interco. I need a variable to display the total. My previous code worked for displaying variable totals, but i needed to amend my code, so it take into account Positive and Negative values. How woud i alter my code to display the Total? My previous code can be seen below:
Blockquote
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 _TotalOtherCashFlowfromTradingAdjustments = CALCULATE([Other cash flow from trading adjustments], 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 _TotalDisposalProceedsCashFlowAdjustments = CALCULATE([Disposal proceeds cash flow adjustments], ALL(CashflowDiscriptions))
VAR _TotalCapitalExpenditure = CALCULATE([Capital expenditure], ALL(CashflowDiscriptions))
VAR _TotalWorkingCapitalBudgetFlex = CALCULATE([WorkingCapitalMvtsInclProvisions], ALL(CashflowDiscriptions))
VAR _TotalCashFlowfromTrading = CALCULATE([CashFlowFromTrading], 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 = “Other cash flow from trading adjustments”, _TotalOtherCashFlowfromTradingAdjustments,
SelectedCashFlowDescription = “Disposals & impairment of fixed assets”, _TotalDisposalsImpairmentofFixedAssets,
SelectedCashFlowDescription = “Working capital mvts excl. provisions”, _TotalWorkingCapitalMvtsExclProvisions,
SelectedCashFlowDescription = “Working capital mvts incl. provisions”, _TotalWorkingCapitalMvtsInclProvisions,
SelectedCashFlowDescription = “Cash flow from trading”, _TotalCashFlowfromTrading,
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 Brambles Limited",
"FX on foreign currency denominated debt",
"Cost sharing payments to Brambles Limited",
"Cash paid for share buyback",
"Equity raised cash flow adjustment",
"Cash flow from ops - management",
"Cash flow from trading",
"Change in capex creditors",
"Profit before Brambles 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 cash flow from trading adjustments",
"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",
"Brambles allocations not in mgt cash flow",
"Interco interest and guarantee fees",
"Interco cash flows",
"Interco royalties",
"Interco cash flow adjustments",
"Internal restructuring",
"Interco dividends Total",
"Change in interco balances",
"Change in interco recharge clearing",
"FX on interco debt",
"Interco capital returned",
"Interco cash flow adjustments",
"Interest expense Total",
"Interest revenue",
"Interest paid",
"Interest received",
"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",
"Disposal proceeds cash flow adjustments",
"Brambles 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”,
“Entity acquisition cash flow adjustments”,
“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”,
“Deferred settlements now received”,
“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”,
“Pension plan adjustment”,
“Pension plan adjustment”,
“Working capital - budget flex”,
“Pooling equipment additions”,
“Pooling equipment replacements”,
“Pooling equipment internal transfers”,
“Other PP&E additions”,
“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”,
“FX adjustments to cash flow”,
“Change in cash net of overdraft”
}
)
)
Blockquote
