Multiple Payment Methods in one Transaction

[Jayshamone]

Hello,

we want to give our customers an overview of what they have purchased using their individual voucher code.

The basic setup would be this:
There is a transactions table connected to a trans_payments-table which again is connected to a payment table.

Since a transaction can be paid for using several payment methods (a part by a voucher, another part cash, ...).

In SQL you could simpy write it like this:

How can this be done in Analysis Services?

The layout here is a bit different. For better structure the voucher codes are written in the payment table.

However - if they filter their code - the customers see their whole transaction and all articles they bought.

But the amount paid does not match the sum of the transaction values, if multiple payment methods were used.

How can I model this in Analysis Services to depict these transactions?

Thank you

Justus

[Devavella]

I had a similar case where I needed to handle split payments. What worked for me was treating each method as a separate line item with the same transaction ID, then grouping by that ID in Power BI. That way I could sum totals correctly without double-counting. For my custom payment flow with Naver Pay, I synced the logic with https://docs.antom.com/ac/antomop/naverpay to keep data consistent.

[Jayshamone · Accepted solution]

I found a solution which gets the correct results, but might be rather inefficent when handling large amount of data.

Basically, you can use another table including all combinations of payment ID and transaction ID which you can use for filtering. Relate that to the transactions table and leave the rest as it is. This should grant you the correct results.

Thanks for your help

If you have any idea how to manage this more efficiently, I'd be thankful

[Anonymous]

Hello @Jayshamone ,
Can you please provide the sample data after removing the sensitive data?
What is the use of cross join here?

[Jayshamone]

Thanks for your quick response.

Without the crossjoin, I gett all the transactions in the selected timeframe.

Here are some sample data for one voucher code:

[amitchandak]

@Jayshamone , One way I can think at high level is to merge Trans_payment + Payments into one table and use it

[Jayshamone]

But if I filter the voucher code within the merged table, will I not still only get the payments from the voucher and not the other payment methods?