Forum Discussion
Helper IHow 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
Helper IHello 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.
Super UserHello 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 ResultIt 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 ResultI hope this helps to clarify the issue.
Thanks,
Udit