topic How to subtract...? in DAX Commands and Tips

How to subtract...?

hansreivers — Mon, 07 Oct 2024 13:30:32 GMT

Hi all,

I need help. These are some random figures from 2024 and 2023. Where you see the Total, you see the summarized values of 2024 + 2023. I need to subtract them in the total. Is this possible in an easy way?

Re: How to subtract...?

dharmendars007 — Mon, 07 Oct 2024 13:45:48 GMT

Hello hansreivers ,

At the total level you need to change the logic, please try the below measure..

AdjustedTotalMeasure =
IF(
NOT(ISINSCOPE('Table'[Year])), -- Checks if the calculation is in the total row
SUM('Table'[2024_Column]) - SUM('Table'[2023_Column]), -- Calculation for the total row
SUM('Table'[2024_Column]) + SUM('Table'[2023_Column]) -- Regular sum for individual years)

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

Re: How to subtract...?

quantumudit — Mon, 07 Oct 2024 13:46:54 GMT

Hello hansreivers

I hope I have understood your issue correctly. If you want to subtract the yearly values in the "Total" column instead of the default summation, you can use the following measure:

Values = VAR _val2024 = CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Year] = 2024 ) VAR _val2023 = CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Year] = 2023 ) RETURN IF ( ISINSCOPE ( 'Table'[Year] ), SUM ( 'Table'[Value] ), _val2024 - _val2023 )

I have also included a snapshot of the solution here:

If this doesn't solve your issue, please consider providing a sample dataset and a desired solution sample.

Best Regards,
Udit

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

Re: How to subtract...?

hansreivers — Mon, 07 Oct 2024 14:04:26 GMT

Hi both, thanks for your replies. I tried the solution of quantumudit and this works when I use the SUM of the turnover, but is this also possible when I use a measure?

My measure is;

Turnover = CALCULATE (SUM( table[turnover])

Re: How to subtract...?

hansreivers — Mon, 07 Oct 2024 14:07:37 GMT

I'm sorry... it works (off course) 🙂

Re: How to subtract...?

hansreivers — Mon, 07 Oct 2024 14:36:07 GMT

Hi quantumudit ,

I used this measure;

Values =

VAR _valthisyear = CALCULATE ([turnover], 'Calendar'[Year Index] = 0 )

VAR _valprioryear = CALCULATE ([turnover], 'Calendar'[Year Index] = -1 )

VAR Result = IF (

ISINSCOPE ( 'Calendar'[Year Index] ), [turnover],

_valthisyear - _valprioryear

)

RETURN

Result

When I return .. this year, 2024 nicely pops up, when I return prior year, 2023 nicely pops up, but when I return result I get this...

Before the 2023 values, there is a minus and I don't want that...

I also want to try to get by the totals a black value when the difference is positive and a red value when the difference is negative. Is that possible?

Many thanks in advance!

Re: How to subtract...?

quantumudit — Mon, 07 Oct 2024 14:47:51 GMT

Hello hansreivers

Seems like you are getting the results correctly but, want to color code it based on certain condition. Is that right? Correct me if I'm wrong understanding the issue...

Re: How to subtract...?

hansreivers — Mon, 07 Oct 2024 15:10:49 GMT

Hello quantumudit ,

I have 2 questions;

* I want to have the totals in black or red (positive / negative)

* I don't understand the measure with the variable's. I think when I refer to this year minus prior year, the values of year 2023 (= year index -1), should also also be positive. When I return only 2023 (year index -1), I get those positive numbers. When I return Result, I get 2024 positive, 2023 negative and total is the correct subtraction. But I don't understand why than 2023 is negative...

EDIT:

When I use 2024 and 2023, the measure works perfect. When I use year index 0 and year index -1, the year 2023 (year index minus 1) shows constant the negative value in stead off the normal positive value for 2023.

Re: How to subtract...?

quantumudit — Mon, 07 Oct 2024 17:00:19 GMT

Hello hansreivers

For colours, you can create a measure similar to the following:

Values Colors = VAR _color = IF([Values] < 0, "#F47174", "#000000") RETURN IF ( ISINSCOPE ( 'Table'[Year] ), "#000000", _color )

Then, you can apply it to the "Value" field as shown in the GIF below:

Regarding the negative numbers, I'm not sure why you are getting negative values; it shouldn't be the case. Please provide a sample of the dataset (anonymized) so that I can have a better look at the data structure and provide you with the appropriate measure that should work.

Thanks!

Re: How to subtract...?

hansreivers — Tue, 08 Oct 2024 08:03:32 GMT

I've send you a message.

Re: How to subtract...?

quantumudit — Tue, 08 Oct 2024 19:23:57 GMT

Hello hansreivers

Please use this formula instead to get the desired result:

Values Year Index = VAR _valty = CALCULATE ([Omzet], 'Calendar'[Year Index] = 0 ) VAR _valpy = CALCULATE ([Omzet], 'Calendar'[Year Index] = -1 ) VAR Result = IF ( ISINSCOPE ( 'Calendar'[Year] ), [Omzet], _valty - _valpy ) RETURN Result

It seems that you are using "ISINSCOPE (' Calendar '[Year Index])" instead of "ISINSCOPE (' Calendar '[Year])". This seems to be the root cause of the problem.

In the rows, we have the 'Calendar'[Year] column where we check the scope, not 'Calendar'[Year Index].

As a result, the DAX formula you are using is incorrect. Here is your DAX formula:

I hope this helps to clarify the issue.

Thanks,

Udit