Forum Discussion

hansreivers's avatar
hansreivers
Helper I
1 year ago
Solved

How to subtract...?

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 possibl...
DAX
Help
Need Help
subtract filtered value
subtraction of Row
  • quantumudit's avatar
    quantumudit
    1 year ago

    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

quantumudit's avatar
quantumudit
Super User
1 year ago

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's avatar
hansreivers
Helper I
1 year ago

I've send you a message.

  • quantumudit's avatar
    quantumudit
    Super User
    1 year ago

    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