Difference column in a matrix

[homboy27]

Client	Hours	Revenue
X	123	20000
Y	100	10000
	23	10000

I created the above table in PowerBI. How do i create the 3rd column that subtracts client x from y?

[Ashish_Mathur]

Hi,

Assuming Hours and Revenue are measures, try this measure

=[Revenue]-CALCULATE([Revenue],Data[Client]="Client X")

Hope this helps.

[homboy27]

That didnt work. Also hours and revenues are not measures.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.  Also show the expected result.

[parry2k]

@homboy27 add two new measures as belowo, drop table visual with Client, Total Hours, Total Revenue and you will have your solution

Total Revenue = 
IF(  ISINSCOPE( Table3[Client] ),
    SUM( Table3[Revenue] ) ,
    CALCULATE( SUM( Table3[Revenue] ), Table3[Client] = "X" ) -
    CALCULATE( SUM( Table3[Revenue] ), Table3[Client] = "Y" )
)

Total Hours = 
IF(  ISINSCOPE( Table3[Client] ),
    SUM( Table3[Hours] ) ,
    CALCULATE( SUM( Table3[Hours] ), Table3[Client] = "X" ) -
    CALCULATE( SUM( Table3[Hours] ), Table3[Client] = "Y" )
)

[homboy27]

That did not work either

[parry2k]

@homboy27 you have to provide more information, just saying, it didn't work, doesn't help to understand what is not working. It is very hard to judge what you are seeing and getting, so provide more information if you need help.

here is how it looks at my end

[homboy27]

it says calculation error. column is part of composite key, but not all columns of the composite key are included in the expression  or its dependant expression

[homboy27]

Sorry let me more clear. i am trying to subtract from same columns. For example i want to add up total hours for client x  and client y and i would like to subtract them. i also want to do the same for reveue.

Client	Hours	Revenue
X	1	12
X	2	12
X	3	334
X	4	34
Y	5	34
Y	6	343
Y	7	43
Y	8	34

[parry2k · Accepted solution]

@homboy27 the solution I proposed should work, doesn't matter number of records. Pbix is attached for your reference.

[homboy27]

Client	Hours	Revenue
X	123	20000
Y	100	10000
Expected Result	23	10000

please see above. i created a table with the first 3 rows. Expected result column is what i would like to create. It basically subtracts the value from columns 2 and 3.

[homboy27]

I created the first 2 columns and would like to create the 3rd column that subracts the first 2 columns