How to calculate the amount retained based on conditions

[Milan14]

Hello everyone,

I would like to calculate the amount retained from date slicer, selected 1/7/2021 until 3/7/2021, only include those USERID who performed top-up in the same period. Also, I would like to show the amount retained = 0 for those users who performed payment more than the top-up amount.

Below shows the formula I used to calculate the amount retained:

Amount retained = Top-up amount – payment amount

Sample table:

Expected outcome in table:

Expected outcome in card:

Amount Retained

70

Thanks.

[VahidDM]

Hi @Milan14 

Try these 3 measures:

Amount Retained =
VAR _TP =
    CALCULATE ( MAX ( 'Table'[Amount] ), 'Table'[Transaction Type] = "TopUp" )
VAR _PY =
    CALCULATE ( MAX ( 'Table'[Amount] ), 'Table'[Transaction Type] = "Payment" )
VAR _Dif = _TP - _PY
RETURN
    IF ( _Dif < 0, 0, _Dif )

Payment Amount =
VAR _TP =
    CALCULATE ( MAX ( 'Table'[Amount] ), 'Table'[Transaction Type] = "TopUp" )
VAR _PY =
    CALCULATE ( MAX ( 'Table'[Amount] ), 'Table'[Transaction Type] = "Payment" )
RETURN
    IF ( _TP = 0, 0, _PY )

Top-Up Amount =
CALCULATE ( MAX ( 'Table'[Amount] ), 'Table'[Transaction Type] = "TopUp" )

Output:

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

Appreciate your Kudos !!

[amitchandak]

@Milan14 , Create a measure like

measure =
var _1 = calculate(sum(Table[Amount]), filter(Table, Table[Transaction_type] ="TopUp"))
var _2 = calculate(sum(Table[Amount]), filter(Table, Table[Transaction_type] ="Payment"))
return
sumx(Values(Table[UserID]), if(_1>_2, _1 -_2, blank()))

[Milan14]

Hi @amitchandak 

Thanks for your suggestion. I have tried out with the formula you provided, but It doesn't work out.

This is the outcome I get with the formula you provided:

This is my expected outcome in table:

Have any idea what kind of formula I can apply to solve this issue?