topic Re: Dax measure to subtract one value from another without changing other values in DAX Commands and Tips

Dax measure to subtract one value from another without changing other values

L_K_ — Fri, 29 Jul 2022 11:28:43 GMT

Hello!

I need some help with displaying my data in the matrix.

I want to create a dax measure to subtract one value from another without changing other values as shown in the added picture.

I have an idea how the dax should be constructed but I can’t figure it out, so every help would be very appreciated.

Re: Dax measure to subtract one value from another without changing other values

tamerj1 — Fri, 29 Jul 2022 11:40:37 GMT

Hi L_K_
You can use something like

Wanted Result = SUMX ( SUMMARIZE ( TableName, TableName[Grooup], TableName[ColumnName] ), CALCULATE ( VAR CurrentValue = SUM ( TableName[Value] ) VAR Value40R = CALCULATE ( SUM ( TableName[Value] ), TableName[ColumnName] = "00040 R" ) RETURN IF ( SELECTEDVALUE ( TableName[ColumnName] ) = "00001 P", CurrentValue - Value40R, CurrentValue ) ) )

Re: Dax measure to subtract one value from another without changing other values

L_K_ — Fri, 29 Jul 2022 12:03:46 GMT

Thank you for your help, but I'm not getting the right numbers.

Re: Dax measure to subtract one value from another without changing other values

tamerj1 — Fri, 29 Jul 2022 12:13:11 GMT

L_K_
What is value? Is it a measure or a summarized column? I also see year at the left top corner. Are you slicing also by year? From which table? Please provide more context with some screeshots for better undertanding. Also please provide a screenshot of you are getting now and what are you expecting to see.

Re: Dax measure to subtract one value from another without changing other values

L_K_ — Fri, 29 Jul 2022 12:32:44 GMT

Hello tamerj1 ,

I'm sorry but I don't know how to mask so much data to show you everything. The groups and names are in a different table than the values, they are connected by an ID and the values in the matrix are a measure. The measure has a bunch of filters and everything is also filtered by year and month from a date table.

I thought about making a measure that gives every group name value the number value of 00040 R, and then make an if statement that when the name is 00001 P it subtracts the 00040 R value if true, and if false it gives the original numbers. But I don’t know how to give every name the same value. If it makes sense.

I’m sorry I can’t provide you with all the information and I am really thankful for your response.

Re: Dax measure to subtract one value from another without changing other values

tamerj1 — Fri, 29 Jul 2022 12:38:29 GMT

L_K_

Are groups and Names in the same table? What are the relationships between the tables?

Re: Dax measure to subtract one value from another without changing other values

L_K_ — Fri, 29 Jul 2022 12:57:51 GMT

tamerj1

The tables kind of look like this

Table 1 - one to many * Table 2

Date table - one to many * Table 2

Re: Dax measure to subtract one value from another without changing other values

tamerj1 — Fri, 29 Jul 2022 13:15:37 GMT

Hi L_K_

please try

Wanted Result = SUMX ( CROSSJOIN ( VALUES ( DateTable[Year] ), SUMMARIZE ( Table1, Table1[Group], Table1[Name] ) ), CALCULATE ( VAR CurrentValue = SUM ( Table2[Value] ) VAR Value40R = CALCULATE ( SUM ( Table2[Value] ), Table1[Name] = "00040 R", ALL ( Table1[Group] ) ) RETURN IF ( SELECTEDVALUE ( TableName[ColumnName] ) = "00001 P", CurrentValue - Value40R, CurrentValue ) ) )

Re: Dax measure to subtract one value from another without changing other values

L_K_ — Mon, 01 Aug 2022 07:33:51 GMT

Hello, tamerj1

Sorry for my late reply. The sum value is now correct, it subtracts the value correctly and displays the right amount, but the displayed value for 0001 P is not changed.

Re: Dax measure to subtract one value from another without changing other values

tamerj1 — Mon, 01 Aug 2022 08:04:33 GMT

Hi L_K_
Would you please share a screanshot of the results you are getting as well as the code you have used?

Re: Dax measure to subtract one value from another without changing other values

L_K_ — Mon, 01 Aug 2022 08:26:04 GMT

TEST3 =

SUMX (

CROSSJOIN (

VALUES ( 'Date'[Year] ),

SUMMARIZE (Table1,Table1[Group],Table1[Name] )

CALCULATE (

VAR CurrentValue =

CALCULATE(SUM(Table2[Value]),'Date'[Month]=SELECTEDVALUE('Date'[Month]))

VAR Value40R =

CALCULATE (

SUM ( Table2[Value] ),

Table1[Name] = "00040 R",

ALL ( Table1[Group] ) ,'Date'[Month]=SELECTEDVALUE('Date'[Month])

)

RETURN

IF (

SELECTEDVALUE ( Table1[Name] ) = "00001 P",

CurrentValue - Value40R,

CurrentValue

)

Re: Dax measure to subtract one value from another without changing other values

tamerj1 — Mon, 01 Aug 2022 08:47:43 GMT

L_K_
please try measure

SELECTEDVALUE ( Table1[Name] )

please let me know what you get

Re: Dax measure to subtract one value from another without changing other values

L_K_ — Mon, 01 Aug 2022 08:51:52 GMT

The names (text) inside the group, like: 00002 O, 00003 D, 00040 R, 0001 P...

Re: Dax measure to subtract one value from another without changing other values

tamerj1 — Mon, 01 Aug 2022 08:57:12 GMT

L_K_
Ok please try

TEST3 = SUMX ( CROSSJOIN ( VALUES ( 'Date'[Year] ), SUMMARIZE ( Table1, Table1[Group], Table1[Name] ) ), CALCULATE ( VAR CurrentValue = CALCULATE ( SUM ( Table2[Value] ), 'Date'[Month] = SELECTEDVALUE ( 'Date'[Month] ) ) VAR Value40R = CALCULATE ( SUM ( Table2[Value] ), Table1[Name] = "00040 R", ALL ( Table1 ), 'Date'[Month] = SELECTEDVALUE ( 'Date'[Month] ) ) RETURN IF ( SELECTEDVALUE ( Table1[Name] ) = "00001 P", CurrentValue - Value40R, CurrentValue ) ) )

Re: Dax measure to subtract one value from another without changing other values

L_K_ — Mon, 01 Aug 2022 09:04:56 GMT

This works perfectly!

Thank you so so so much tamerj1 for your time and help!