Subtracting the sum of filtered values in one column

[Anonymous]

Hello,

I need do select two cenarios in ONE filter and have the subtraction of the sum of each of then.

Like, if I select A and B, it would be (1+2+3)-(4+5) = -3

Thanks!

Cenario	Value
A	1
A	2
A	3
B	4
B	5
C	6
C	6

[dax · Accepted solution]

Hi  @Anonymous , 

You need to change something in @amitchandak 's expression, you could try below expression 

Measure =
VAR _min =
    MINX ( 'Table', 'Table'[Cenario] )
VAR _max =
    MAXX ( 'Table', 'Table'[Cenario] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Cenario] = _min )
    )
        - CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER ( ALL ( 'Table' ), 'Table'[Cenario] = _max )
        )

You could refer to my sample for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Try like

Measure =
 var _min = minx(Table,Table[Cenario])
 var _max = maxx(Table,Table[Cenario])
 return 
 calculate(Table[Value],filter(all(Table),Table[Cenario]=_min))- calculate(Table[Value],filter(all(Table),Table[Cenario]=_max))
 

or

  Measure =
 var _min = minx(Table,Table[Cenario])
 var _max = maxx(Table,Table[Cenario])
 return 
 calculate(Table[Value],filter(allselected(Table),Table[Cenario]=_min))- calculate(Table[Value],filter(allselected(Table),Table[Cenario]=_max))