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

[L_K_]

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.

[tamerj1]

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
            )
    )
)

[L_K_]

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

[tamerj1]

@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.

[L_K_]

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.

[tamerj1]

@L_K_ 

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

[L_K_]

@tamerj1 

The tables kind of look like this

Table 1 - one to many * Table 2

Date table - one to many * Table 2

[tamerj1]

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
            )
    )
)

[L_K_]

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.

[tamerj1]

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

[L_K_]

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

            )

    )

)

[tamerj1]

@L_K_ 
please try measure

SELECTEDVALUE ( Table1[Name] )

please let me know what you get

[L_K_]

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

[tamerj1 · Accepted solution]

@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
            )
    )
)

[L_K_]

This works perfectly!

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