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
Super UserHello 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...
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.
- quantumudit
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
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