Substract column with a constant from another column

[AN2021]

Hi, I want to calculate as follow:

Minus "Value" for each bucket with "Value" from bucket 1 group by "Division"

Division	Bucket	Value	Dif
A	1	7	0 (7 - 7)
A	2	8	1 (8 - 7)
A	3	9	2 (9 - 7)
B	1	10	0 (10 - 10)
B	2	13	3 (12 - 10)
B	3	15	5 (15 - 10)

I try to use this DAX, but it return to sum up all "Value" from Bucket = 1 from all Division.

Total Value = SUM(MY_TABLE[Value])

Dif =

CALCULATE(

[Total Value],

FILTER(

ALL(MY_TABLE),

MY_TABLE[Bucket] = 1

)

)

Division	Bucket	Value	Dif
A	1	7	-7 (7 - 17)
A	2	8	-9 (8 - 17)
A	3	9	-8 (9 - 17)
B	1	10	-7 (10 - 17)
B	2	13	-3 (12 - 17)
B	3	15	-2 (15 - 17)

[Fowmy · Accepted solution]

@AN2021 

Try this code please:

Difference = 
Table3[Value]
-
CALCULATE(
    sum(Table3[Value]),
    Table3[Bucket] = 1,
    ALLEXCEPT(Table3,Table3[Division])
) 

[themistoklis · Accepted solution]

@AN2021 

It still works with new dimensions.

See attached file.

If you could share you file so as to have a look at it that would be great

[Fowmy]

@AN2021 

Add the following column:

Dif = Table3[Value] - CALCULATE( MIN(Table3[Value]), ALLEXCEPT(Table3,Table3[Division]))

[AN2021]

Hi Fowmy

Actually I want to get the "Value" from Bucket = 1, not the Min of "Value". Your solution might work with my example, but in my actual case some "Value" in Bucket = 1 is not the smallest one.

[Fowmy · Accepted solution]

@AN2021 

Try this code please:

Difference = 
Table3[Value]
-
CALCULATE(
    sum(Table3[Value]),
    Table3[Bucket] = 1,
    ALLEXCEPT(Table3,Table3[Division])
) 

[themistoklis]

@AN2021 

Try the following measure:

Measure3 =
VAR __bucket1 =
    CALCULATE (
        SUM ( Sheet1[Value] ),
        FILTER ( ALLEXCEPT ( Sheet1, Sheet1[Division] ), Sheet1[Bucket] = 1 )
    )
RETURN
    SUM ( Sheet1[Value] ) - __bucket1

Workspace attached on this message

[AN2021]

Hi themistoklis

Its work, but wonder why if I have more column, its not works?

From your example I add 1 column named Dim1

[themistoklis · Accepted solution]

@AN2021 

It still works with new dimensions.

See attached file.

If you could share you file so as to have a look at it that would be great

[AN2021 · Accepted solution]

Oh my bad, yeap its work, thanks

[Fowmy]

@AN2021 

You can click on @themistoklis solution as Accepted, this will help others find it the correct reply.