Forum Discussion
Calculate operations after certain value
Hi everyone, I have some trouble trying to do an specific topic, here is my data:
| ID | Register | Type | Amount |
| 1 | 01/01/2018 | Balance | 5 |
| 2 | 01/01/2018 | Cash | 10 |
| 3 | 01/01/2018 | Cash | 15 |
| 1 | 02/01/2018 | Balance | 20 |
| 2 | 02/01/2018 | Balance | 5 |
| 3 | 02/01/2018 | Balance | 10 |
| 1 | 04/01/2018 | Balance | 8 |
| 2 | 04/01/2018 | Cash | 10 |
| 3 | 04/01/2018 | Balance | 15 |
| 1 | 05/01/2018 | Cash | 10 |
| 2 | 05/01/2018 | Balance | 10 |
| 3 | 05/01/2018 | Balance | 20 |
ID: id user
Register: when a user buy an item
Type: type of payment
Amount
When someone wants to buy in the store, we give them an amount of 20 (Balance),the the user can buy some money in cash and add to balance.
I want to calculate the operations after a user spend that money (no matter type of payment). For example, user ID 1, spend the money on 2nd transaction, then he has 2 more transactions.
The result I expect is:
| ID | Total Payment | Total Balance payment | Total Operations | OperationsAfter20 |
| 1 | 43 | 33 | 4 | 2 |
| 2 | 35 | 15 | 4 | 0 |
| 3 | 60 | 45 | 4 | 1 |
Thank you so much guys!
Hi，Anonymous
You can use the following method to meet your needs:
Create a measure for Cumulative Balance to get OperationsAfter20 like this:
Cumulative Balance =
CALCULATE (
SUM ( 'Table'[Amount] ),
FILTER (
ALL ( 'Table' ),
'Table'[ID] = MAX ( 'Table'[ID] )
&& 'Table'[Type] = "Balance"
&& 'Table'[Register] <= MAX ( 'Table'[Register] )
)
)
OperationsAfter20 =
VAR _date =
CALCULATE (
MIN ( 'Table'[Register] ),
FILTER (
ALL ( 'Table' ),
'Table'[ID] = MAX ( 'Table'[ID] )
&& [Cumulative Balance] > 20
)
)
RETURN
COUNTROWS (
FILTER (
ALL ( 'Table' ),
'Table'[ID] = MAX ( 'Table'[ID] )
&& [Cumulative Balance] > 20
&& 'Table'[Register] > _date
)
) + 0
Try to create new measure for total balance payment, Total operations, Total payment.
Total Balance payment =CALCULATE(
SUM('Table'[Amount]),
FILTER('Table','Table'[Type]="Balance"))
Total Operations = COUNT('Table'[Amount])
Total Payment = SUM('Table'[Amount])
Then you will get what you want.
Here is the demo , please try it:
Hope it helps.
Best Regards,
Caitlyn Yan
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Anonymous , 4th one is not clear. Try measures like
Total Payment = sum(Table[Amount])Total Balance payment = calculate(sum(Table[Amount]), filter(Table, Table[Type] ="Balance"))
Total Operations = count(Table[Amount])
OperationsAfter20 = calculate(count(Table[Amount]), filter(Table,Table[Register] > calculate(maxX(filter(Table, Table[Type] ="Balance" && Table[Amount] =20), Table[Register]), allexpcept(Table,Table[place_digital_adress]))))
Hi，Anonymous
You can use the following method to meet your needs:
Create a measure for Cumulative Balance to get OperationsAfter20 like this:
Cumulative Balance =
CALCULATE (
SUM ( 'Table'[Amount] ),
FILTER (
ALL ( 'Table' ),
'Table'[ID] = MAX ( 'Table'[ID] )
&& 'Table'[Type] = "Balance"
&& 'Table'[Register] <= MAX ( 'Table'[Register] )
)
)
OperationsAfter20 =
VAR _date =
CALCULATE (
MIN ( 'Table'[Register] ),
FILTER (
ALL ( 'Table' ),
'Table'[ID] = MAX ( 'Table'[ID] )
&& [Cumulative Balance] > 20
)
)
RETURN
COUNTROWS (
FILTER (
ALL ( 'Table' ),
'Table'[ID] = MAX ( 'Table'[ID] )
&& [Cumulative Balance] > 20
&& 'Table'[Register] > _date
)
) + 0
Try to create new measure for total balance payment, Total operations, Total payment.
Total Balance payment =CALCULATE(
SUM('Table'[Amount]),
FILTER('Table','Table'[Type]="Balance"))
Total Operations = COUNT('Table'[Amount])
Total Payment = SUM('Table'[Amount])
Then you will get what you want.
Here is the demo , please try it:
Hope it helps.
Best Regards,
Caitlyn Yan
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
