topic Re: DAX expression for subtracting 3 measures in DAX Commands and Tips

DAX expression for subtracting 3 measures

Anonymous — Wed, 05 May 2021 15:23:05 GMT

Hi team

I'm hoping I can have some help. I know this achievable as I've done this before but unfortunately an IT mishap has led to me losing my data.

Here is an example of data:

I want to achieve the formula of Owes- Repaid - Written off to give me the amount remaining. The issue is that the repaid and written off values are measures and everything I try using SUMX or CALCULATE results in a circular dependency. I have literally spent all day trying to find the answer on here and I can't find it- I know I did a nested calculate/SUMX formula or something before. Everything I've found so far on the formula is coming up with the circular dependency.

Please help! It's driving me crazy.

Thanks in advance 🙂

Re: DAX expression for subtracting 3 measures

amitchandak — Wed, 05 May 2021 15:45:58 GMT

Anonymous , as the last two are measures , this should work

Sum(Table[Owes]) - [Repaid] - [Written off]

If not

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: DAX expression for subtracting 3 measures

Anonymous — Wed, 05 May 2021 18:07:59 GMT

Good evening! Thank you for trying to help. Sadly that one gives me a Sum function only accepts column reference as an argument. So I amended to:

=SUM('Table'[Owes])-SUM('Table'[repaid] etc and that worked but gave me the grand total against every row.

What I really need is the SUMX function to give me the total per row instead!

=SUMX('Table'[Owes])-('Table'[repaid]

Gives me the same as above

I tried using the filter to filter by Number to give me just the figure per employee, and I tried the calculate function to filter but still to no avail.

Sadly I can't share the data due to confidentiality, but any suggestions would be very, very well received!!! 🙂

Re: DAX expression for subtracting 3 measures

Anonymous — Thu, 06 May 2021 14:29:44 GMT

Right, I've managed to figure it out and I want to share just in case other people have the same issue.

The issue was my written off value was pulling a value based on the 'owes' amount. I scrapped this and started again with a boolean value instead, 1 for written off and 0 for not written off. This way I'm not causing a circular issue.

I then used this expression:

CALCULATE(SUMX('Table', 'Table'[Owes]-'Table'[Repaid]),'Table'[Written Off]=0)

Which then gave me the total remaining figure per employee.

Thank God.