Forum Discussion
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 possible in an easy way?
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
10 Replies
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: LinkTreeHi 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])
- 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)RETURNResultWhen 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!
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...
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
I'm sorry... it works (off course) 🙂
