issues with RETURN statement from variables
Hi Community im trying to create a DAX calculation that evaluates invoiced quantity based on my "Selection" variable. My issue is the return statement, where it wont divide my number with 1000 which i use to convert from kg to tonnes. How can i change my code to. Any tips for the overall structure of my code will also be helpful. Se snippet below for context. Thank you!
Dynamic Percentage of Parent Switch Order
Hi all, I am trying to do a percentage of parent calculation which is fine but I am using field parameters so users can dynamically change the row selections in a matrix. If the rows are selected in a specific order then the calculation works fine but if they are selected out of order with the switch calculation then the wrong result is returned. I created a dummy table to represent my issue where there are 3 categories, brand, group and country. DAX formula is: "%Share = VAR _brand = CALCULATE([SUMVALUE],ALLSELECTED('Table'[Brand])) VAR _country = CALCULATE([SUMVALUE],ALLSELECTED('Table'[Country])) VAR _group = CALCULATE([SUMVALUE],ALLSELECTED('Table'[Group])) RETURN SWITCH(TRUE(), ISINSCOPE('Table'[Brand]),DIVIDE([SUMVALUE],_brand), ISINSCOPE('Table'[Group]),DIVIDE([SUMVALUE],_group), ISINSCOPE('Table'[Country]),DIVIDE([SUMVALUE],_country), DIVIDE([SUMVALUE],CALCULATE([SUMVALUE],ALLSELECTED('Table'))) )" If I select country as first level in matrix then group as second level, it works correctly for both levels of the matrix. If I select group then country the second level is wrong. Presumably the group VAR is passing as true first giving sum of all groups in the selected country. So my question is, how can I get it to select the correct VAR regardless of the order the field parameters are selected? Dummy table I created here: Brand Group Country Value Brand1 Group1 GB 37 Brand2 Group1 GB 16 Brand3 Group2 GB 16 Brand4 Group2 GB 21 Brand5 Group2 GB 21 Brand6 Group3 GB 47 Brand7 Group3 GB 41 Brand8 Group3 GB 24 Brand9 Group3 GB 25 Brand1 Group1 FR 21 Brand2 Group1 FR 49 Brand3 Group2 FR 41 Brand4 Group2 FR 22 Brand5 Group2 FR 19 Brand6 Group3 FR 37 Brand7 Group3 FR 27 Brand8 Group3 FR 16 Brand9 Group3 FR 37 Brand1 Group1 DE 9 Brand2 Group1 DE 31 Brand3 Group2 DE 17 Brand4 Group2 DE 21 Brand5 Group2 DE 50 Brand6 Group3 DE 49 Brand7 Group3 DE 33 Brand8 Group3 DE 34 Brand9 Group3 DE 40
Using Variables to display Total/Sum
I am using the dax below to display figures from a table: Measure = VAR NegativeItems = { "Profit before Brambles 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 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. 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 The rows displays value where it is avaialble: 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: 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" } ) )Use Selectedvalue (from 2 different slicers) to creating a cumulative metric with DAX
Hi, I hope you can help me as I have been trying to do this for days and somehow it doesnt work. I have a data table like the example below (this is just an example), a Calendar table and a Parameter (Reference Plan). I would like to use 2 slicers, one with the Calendar[Month Name] and the other with the Paramenter[RefNo], so wehn I filter for example Month= February and Reference Plan = February I will get the cumulative sum for those records that are January & February (highlighted in yeloow below). Data table: Paramenter: Calendar: I have created the folloing metric attemps but none work: ACTUALS test = VAR selectedmonth = SELECTEDVALUE('Calendar'[Month]) VAR RefPlan = SELECTEDVALUE(REFERENCE_PLAN[RefNo]) Return CALCULATE([Commitment ACTUALS], FILTER( ALLSELECTED(financialmonthlystatus), financialmonthlystatus[Month (Period)] <= selectedmonth && financialmonthlystatus[creationperiod] <= RefPlan) ) This metric only returns when period and creation period are February = 4 Please note [Commitmet Actuals] is a metric sumarizing all amounts and filetring by Group Type = "COMMITMENT" and feature = "ACTUALS". Please let me know if you need more information, Many thanks! ImyNot able to force date format in DAX formula
Hi, I struggle with using date inside a formula. I have tried many different options, none is working. Here is my current formula, somehow it seems like I cannot pull the month and date out from the "Max(date)" The only thing that works is when I hard code the date like this Date(2023, 10, 31), as you can see in my formula I try to add a variable, it does not work. However, this variable works if I write it in a Measure.... The table Calendar is an island, not connected to any other table. And I use a date-slicer for defining what date it is. I have tried to format all dates, both in the Query data loading, and in front view. I have tried using Month(Max(Date)) etc, I have tried Mid(Max(Date),4,2), or Left(Max(Date),2) etc.... I have tried to add new column in my table, new measure... What is the reason for this struggle with date and date format? Best regards E
Calculate with and without variables difference
I have 2 calculate formulas: Formula 1: Emp Exits = CALCULATE( SUM(Emp_Exits[Employee Exits]), Emp_Exits[MonthNo]<=MAX(Emp_Exits[MonthNo]), ALL(Emp_Exits[Month Name]) ) Formula 2: Emp Exits with variable = VAR EmpExits = SUM(Emp_Exits[Employee Exits]) RETURN CALCULATE( EmpExits, Emp_Exits[MonthNo]<=MAX(Emp_Exits[MonthNo]), ALL(Emp_Exits[Month Name]) ) Both the formulas are same except that in Formula 2, i used variable. See the below output screenshot. Formula 1 is giving correct result and formula 2 is not giving the correct result. Why is this behaviour? amitchandak Greg_Deckler tamerj1
