Forum Discussion
How to subtract...?
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
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
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])
- 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 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.
