Forum Discussion
Incorrect measure total
Dear Community,
I have the following measure written for a calculation involving two columns from two tables with a many-to-many relationship.
The first table is the 'Transactions Breakdown Table'. This table comprises various expenses incurred for each portfolio. Some portfolios have only one expense (a single row of data), while others have multiple expenses (multiple rows of data for one item). Each expense has a corresponding account number.
| Portfolio | Expense | Account number |
| abc1 | 100 | 123 |
| abc1 | 100 | 124 |
| abc1 | 100 | 125 |
| abc21 | 100 | 123 |
| abc22 | 100 | 123 |
| abc23 | 100 | 123 |
| abc24 | 100 | 123 |
| abc25 | 100 | 123 |
| abc26 | 100 | 123 |
| abc27 | 100 | 123 |
| abc28 | 100 | 123 |
| abc29 | 10000 | 123 |
The second table is the 'Ownership mapping' table. It displays the percentage share of each client's expenses for each portfolio. A value of 1 represents 100%, and 0.04 signifies 4%. Some portfolios have only one client, while others have more than one.
| Client Ownership | Client | Portfolio |
| 1 | Mary | abc1 |
| 1 | Lily | abc2 |
| 1 | Lily | abc3 |
| 1 | Lily | abc4 |
| 1 | John | abc5 |
| 1 | John | abc6 |
| 1 | John | abc7 |
| 1 | Mary | abc8 |
| 1 | Henry | abc9 |
| 0.04 | Lily | abc10 |
| 0.025 | John | abc10 |
| 0.35 | Jessica | abc10 |
| 0.25 | Zoey | abc10 |
| 0.011 | Aberlene | abc10 |
| 0.183 | Joanna | abc10 |
| 0.076 | Maggie | abc10 |
| 0.065 | Mary | abc10 |
For each expense in every portfolio, I would like to calculate the share of the expense for each corresponding client. However, when I use this measure as values in a matrix table, the grand row total is adding up correctly but the row values are not correct. I went on to create measure 2, which gives me correct row values but incorrect grand row total. Appreciate if anyone could provide any help. Thanks in advance!
1 Reply
Hi,
Based on the 2 tables shared by you, please show the expected result.
