Calculated Column to aggregate two aggregate measures along row level of detail in a matrix

[DonRitchie]

Hi,

This is probably something really simple that I am brainfarting on because it is Monday, but how do I sum the sums of two measures in a calculated column that aligns with row level detail like a measure would?

This is the measure that works: 

Total Pay = sum(CSV[UnattachedSettlement Pay]) + sum (DATASET[AttachedSettlementPay])

 Producing this result:

Rows	Rows	Values	Values	Values
Employee	Week Ending	AttachedSettlementPay	UnattachedSettlementPay	Total Pay
Adam	6/18/22	400	150	550
	7/2/22	0	50	50
Bob	6/4/22	800	300	1100
	6/11/22	1500	0	1500
	6/18/22	1300	50	1350
Chuck	6/25/22	1800	150	1950
	7/2/22	1500	0	1500

I want a Calculated Column that I can put in the Rows fields that produces this result:

Rows	Rows	Rows	Values	Values
Employee	Week Ending	TotalPayColumn	AttachedSettlementPay	UnattachedSettlementPay
Adam	6/18/22	550	400	150
	7/2/22	50	0	50
Bob	6/4/22	1100	800	300
	6/11/22	1500	1500	0
	6/18/22	1350	1300	50
Chuck	6/25/22	1950	1800	150
	7/2/22	1500	1500	0

This does not work, creating the total pay for each driver in the entire database on every row:

TotalPayColumn = CALCULATE(sum(CSV[UnattachedSettlementPay]) + sum(Dataset[AttachedSettlementPay]))

This gives me an error, Cannot convert value 'Adam' of type Text to type True/False.:

TotalPayColumn = CALCULATE(sum(CSV[UnattachedSettlementPay]) + sum(Dataset[AttachedSettlementPay]),DriverCardinal[Driver])

This does not work, creating the total pay for each driver for the displayed weeks on every row (ie, both rows for Adam are 600):

TotalPayColumn = CALCULATE(sum(CSV[UnattachedSettlementPay]) + sum(Dataset[AttachedSettlementPay]),WeekCardinal[Week Ending])

I am sure it is some of those filter functions inside of Calculate, but I have been banging my head on this all morning.  Any help would be appreciated.  Thanks!

[v-jianboli-msft · Accepted solution]

Hi @DonRitchie 

Based on your description, I created a simple sample and relationship :

Here is the DAX of the calculate column:

TotalPayColumn = CALCULATE(SUM('CSV'[UnattachedSettlementPay]),FILTER('CSV',[Employee]=EARLIER('DATASET'[Employee])&&[Week Ending]=EARLIER('DATASET'[Week Ending]))) + [AttachedSettlementPay]

Then You can put the column in Value field to achieve your purpose.

Final output:

If the result is not what you want, please give more details.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

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

[amitchandak]

@DonRitchie , Seem like you are trying to create a column have data from two tables, for that you have to copy values from other tables 

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8