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

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