Forum Discussion
Measure total incorrect
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!
Hi, Yian
You can try the following methods.
Measure 3 = IF(HASONEVALUE('Ownership mapping'[Client]),[Measure 2],[Measure])Is this the result you expect? If not, please provide the output you expect.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Yian , multiplication needs to be done at row level and then needs to be summed up. if not possible then use common columns
preferably from common dimension/on visual columns
Sumx(Values(Dim[Column]), CALCULATE(SUM('Transactions Breakdown'[Total]) * MAX('Ownership mapping'[Client Ownership])) )
Use summarize for more than one column
Hi,
Thanks for the reply. But the solution didn't work out. I just edited my original post and added more information. Please take a look. Thanks very much!
Hi, Yian
You can try the following methods.
Measure 3 = IF(HASONEVALUE('Ownership mapping'[Client]),[Measure 2],[Measure])Is this the result you expect? If not, please provide the output you expect.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
