Measure total incorrect

[Yian]

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!

Measure =

var net_amt = CALCULATE(SUM('Transactions Breakdown'[Expense]))  

var cnt_own = CALCULATE(MAX('Ownership mapping'[Client Ownership]))  

return net_amt * cnt_own

Measure 2 =

SUMX(

    SUMMARIZE(

        'Ownership mapping',

        'Ownership mapping'[Client],

        'Ownership mapping'[Portfolio],

        "TotalOwnership", SUM('Ownership mapping'[Client Ownership])

    ),

    [Measure]

)

[v-zhangti · Accepted solution]

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.

[amitchandak]

@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

[Yian]

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!