How to subtract...?

[hansreivers]

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?

[hansreivers]

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

[quantumudit · Accepted solution]

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

[hansreivers]

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!

[quantumudit]

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...

[hansreivers]

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. 

[quantumudit]

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!

[hansreivers]

I've send you a message.

[quantumudit]

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:

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 Index] ), [Omzet], 
                    _valty - _valpy
    )
RETURN
    Result

I hope this helps to clarify the issue.

Thanks,

Udit

[hansreivers]

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])

[dharmendars007]

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

LinkedIN