Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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 !!
@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()))
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
14 |
User | Count |
---|---|
42 | |
39 | |
33 | |
19 | |
18 |