DAX formula to to sum up certain rows and return that to another row

[richardwg]

Hi Everyone, 

I have the following problem that I can't seem to resolve and would appreciate getting assistance in resolving. The table below represents my data, and all the columns except for client and contract are measures that are calculated from various inputs. I want to sum the profit share for all the clients except 'Internal' and then return that value to the contract profit for the internal client, so in the case below, I'd like to get -2.6 returned to the contract profit.

Client	Contract	Invoice	Service Fee	Rewards Net Service Fee	Operating Cost	Profit	Profit Share	Payment	Contract Profit
Internal	Self	-0.08937	14.82	14.82	14.82	14.80479	0	14.82	xxxxx
Client-1	Con-1	36.92	-0.22139	36.66	36.66	3.816118	-0.78	2.99
Client-2	Con-2	27.43	-0.16449	27.3	27.3	2.594748	-0.52	2.08
Client-2	Con-3	26.39	-0.15853	26.26	26.26	2.483614	-0.52	1.95
Client-3	Con-4	17.55	-0.10523	17.42	17.42	1.157698	-0.26	0.91
Client-1	Con-5	9.23	-0.05538	9.23	9.23	0.95429	-0.13	0.78
Client-1	Con-6	9.23	-0.05523	9.1	9.1	0.951603	-0.13	0.78
Client-1	Con-7	9.23	-0.05573	9.1	9.1	0.950965	-0.13	0.78
Client-4	Con-8	8.97	-0.054	8.97	8.97	0.489909	-0.13	0.39

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[Anonymous · Accepted solution]

Hi @richardwg 

For your question, here is the method I provided:

Since you mentioned that the rest of the columns except "Client" and "Contract" are measures, consider the following dax:

Total Profit Share = 
SUMX(
    FILTER(
        ALL('Table'),
        'Table'[Client] <> "Internal"
    ),
    'Table'[Profit Share]
)

Contract Profit = 
IF(
    SELECTEDVALUE('Table'[Client]) = "Internal",
    'Table'[Total Profit Share],
    BLANK()
)

Here is the result.

Regards,

Nono Chen

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

[richardwg]

@Anonymous  thank you very much, your solution worked perfectly. 💥