Calculations in many-many relationship

[benordinate]

I have two tables in a many-many relationship.

First is a list of bank transactions, with columns:

customer, transaction type, amount

(Transaction types are card, ATM, bank transfer, etc).

Second is a list of bank fees, with columns:

bank, transaction type, fee

I need to join these tables on the transaction type so that for each customer I can see what the total fees would be with each bank if they had the same transactions.

I have joined the tables many to many, but I need to create a column in a matrix visual that calcuates the Bank.Fee * COUNT(CustomerTransactions) (for each transasction type).

How/where do I create such a column ? My attempts so far aren't limiting to the transaction type in the join.

Raw sample data to explain what I need is available here, with a 'desired output' tab https://docs.google.com/spreadsheets/d/1F2nyZp-6AyGFGENKADot8Mp_xV2fgAyvt07fyg4GvQU/edit?usp=sharing

[amitchandak]

@benordinate , better to create a common table for transaction type and similar other dimensions.

refer: https://www.seerinteractive.com/blog/join-many-many-power-bi/

or

https://www.youtube.com/watch?v=Bkf35Roman8

[benordinate]

Thanks for the response @amitchandak  - so the calculated amount I'm trying to add should go in the bridging table I guess?

But I've tried adding a new column:

Total Fee Per Type = COUNT(Transactions[TransactionType]) * SUM(BankCosts[Fee])

but I get the same value in every cell in the matrix - so each value is not filtering on the Transaction type or the bank.

Do you know what I might be doing wrong?

[benordinate]

I've progressed further to find I need to create a measure using SUMX.

Total Fee = SUMX(BankCosts, BankCosts[Fee] * COUNT(Transactions[TransactionType]))

This outputs the correct information in the cells on the matrix. But the total column generates a much higher number. Why might this be?

[v-robertq-msft · Accepted solution]

Hi, @benordinate 

According to your description and DAX formula, it seems that you faced with the problem of the total row of a measure calculation error, you can try to create a new measure like this:

Total Fee_new =

var _new=SUMMARIZE('Table','Table'[Fee Name],"_value",[Total Fee])

return IF(HASONEVALUE('Table'[Fee Name]),[ Total Fee],SUMX(_new,[_value]))

Then you can change the measure with your correct table and column names and go to replace the original measure within your matrix.

And you can check if you can get the total to display what you want.

For more info about the trick, you can check these link:

Dealing with Measure Totals - Microsoft Power BI Community

Solved: How to show total row as sum of distinct count? - Microsoft Power BI Community

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

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