topic Re: Cash flow imbalance n DAX in DAX Commands and Tips

Cash flow imbalance n DAX

Yrstruly2021 — Fri, 16 Feb 2024 14:40:56 GMT

Please assist. The row Cash flow imbalance should be 0 as indicated in the screenshot, below.

I am not getting a 0. Cash flow imbalance Is made up of calculations/formulas as seen in the Excel/Sheet file Column K, https://docs.google.com/spreadsheets/d/1ubo9TAz2zzCpKbfO5SFBMjFLgjFtUxhY/edit?usp=sharing&ouid=104129043494164133703&rtpof=true&sd=true

To get the formula click in column K on the value:

PBIX: https://drive.google.com/file/d/1-wHE0e50nSM9-wSq4dTxStDX58APO_MI/view?usp=sharing

I am also getting a repeat of the amount 201256612.25, I don't know why. Some figures tend to differ when i work connected to the Live source on the Server as opposed to the downloaded version for the Desktop

Re: Cash flow imbalance n DAX

Anonymous — Mon, 19 Feb 2024 01:54:45 GMT

Hi Yrstruly2021 ,

Firstly for the value of your Cash flow imbalance not showing as 0, I think you can do the following.
1. Check that the values in columns K15, K91 and K89 are accurate and correctly linked to the relevant data source.
2. You need to make sure that there are no hidden rows or filters affecting the calculation, and double-check the formulas in the cell where the cash flow imbalance is calculated.
If the problem persists, consider recalculating the entire worksheet or checking for circular references.

Secondly, there is a duplicate amount (201256612.25), I think you can make the following checks and changes.

You need to verify that the data source for this value is consistent. Check to see if any formulas or references are inadvertently duplicating the value. If you are using live data, make sure the data refresh process is working properly. Sometimes, data conversions can lead to unexpected results. Make sure there are no duplicate records or entries that cause duplication. It is also important to check that cell formats (e.g. currency, decimal places) are consistent.

If the problem persists, consider comparing the live data source to the downloaded version to determine if there are any discrepancies.

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

How to Get Your Question Answered Quickly

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Cash flow imbalance n DAX

Yrstruly2021 — Mon, 19 Feb 2024 12:44:42 GMT

When i want to incorporate many DAX codes into one big code, how would i approach this? I believe i use SWITCH or something miliar?

Cash Share Issues by Brambles Limited = SUMX( FILTER( 'ZTBR', 'ZTBR'[Roll_Up_Function] = "Cash share issues by Brambles Limited" ), 'ZTBR'[Amount in USD] ) FX on Foreign Currency Denominated Debt = SUMX( FILTER( 'ZTBR', 'ZTBR'[Roll_Up_Function] = "FX on foreign currency denominated debt" ), 'ZTBR'[Amount in USD] ) Cost Sharing Payments to Brambles Limited = SUMX( FILTER( 'ZTBR', 'ZTBR'[Roll_Up_Function] = "Cost sharing payments to Brambles Limited" ), 'ZTBR'[Amount in USD] )

Re: Cash flow imbalance n DAX

Anonymous — Tue, 20 Feb 2024 08:07:32 GMT

Hi Yrstruly2021 ,

When combining multiple DAX expressions into one cohesive code, you can indeed use the SWITCH function, but it’s essential to consider the overall structure and logic of your calculations.

Instead of having separate measures for each calculation, create a single measure that handles all three scenarios. You can use the SWITCH function to evaluate different conditions and perform the appropriate calculation.

Here’s an example of how you can combine your existing expressions into one using SWITCH.

Total Amount = SWITCH ( TRUE (), 'ZTBR'[Roll_Up_Function] = "Cash share issues by Brambles Limited", SUMX ( 'ZTBR', 'ZTBR'[Amount in USD] ), 'ZTBR'[Roll_Up_Function] = "FX on foreign currency denominated debt", SUMX ( 'ZTBR', 'ZTBR'[Amount in USD] ), 'ZTBR'[Roll_Up_Function] = "Cost sharing payments to Brambles Limited", SUMX ( 'ZTBR', 'ZTBR'[Amount in USD] ), BLANK () // Default value if none of the conditions match )

You can also read this document for the further study: SWITCH function (DAX) - DAX | Microsoft Learn

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.