Create calculated columns by summing values in another table

[sivarajan21]

Hi,

I have been working on this for almost a week now but couldn't figure out.

Below is my sample dataset. 

Missing Dates table

Date	DB Name - Points id
Apr 2,2022	northyorcc-1010
Mar 2,2022	inse-1017

Target table

DBName-Point_id	Target type	Value Type	Value
northyorcc-1010	0	Value_10	-11
northyorcc-1010	0	Value_11	-545
inse-1017	1	Value_01	1
inse-1017	0	Value_02	65
northyorcc-1010	1	Value_03	6
northyorcc-1010	2	Value_03	9
northyorcc-1010	2	Value_03	-3
inse-1017	1	Value_02	10
northyorcc-1010	3	Value_03	12
inse-1017	2	Value_02	10
inse-1017	3	Value_02	10

I am confused with how to create a calculated column in missing dates table. I tried many measures but this confuses me.

I need to create a calculated column called 'Target Units' in Missing Dates table that sums the Target table 'Value' column if their target type is 0 & it corresponds to their DBName_Point_id.

Similarly, calculated column called 'Target Cost' in Missing Dates table that sums the Target table 'Value' column if their target type is 1.

Both the calculated columns are created for their corresponding points id

Expected output would look something like below:

Date	DB Name - Points id	Target units	Target cost
Apr 2,2022	northyorcc-1010	-556	6
Mar 2,2022	inse-1017	65	11

PFA sample file here

Calculated column referencing other tables.pbix

Thanks in advance.

@Ahmedx @grantsamborn @Greg_Deckler 

[Ahmedx]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

https://1drv.ms/u/s!AiUZ0Ws7G26Rh1sdoZSgBgCryq1c?e=wILm1o

[Ahmedx]

pls try this

[Ahmedx]

pls try this

[sivarajan21]

Hi @Ahmedx ,

Many thanks for your patience

Today I had a chance to test this and found the below:

The solution is perfect and it works great. But I need a slight change with a way this measure sums the Target value.

I have this below dataset after testing:

Target table:

DBName-Point_Id	TargetType	Value Type	Value	Source_Num	Source
INSE-792	0	Value_12	659.5999756	4	Budget

Missing Dates table:

Date	DB Name - Points Id	unitTarget
31-Dec-26	INSE-792	15020.32397
30-Dec-26	INSE-792	15020.32397
29-Dec-26	INSE-792	15020.32397

Month Lookup

Value Type	Month
Value_01	January
Value_02	February
Value_03	March
Value_04	April
Value_05	May
Value_06	June
Value_07	July
Value_08	August
Value_09	September
Value_10	October
Value_11	November
Value_12	December

This measure currently sums all the target value for their corresponding DBName_Point_id.But this measure should also sum target value corresponding to their target table Month(Value Type).

The month in Date column should match corresponding month(Value Type) in Target table and get the value.

I am confused how to modify this measure to include the filter condition target table Month(Value Type)

as they don't have month name directly specified(ex:Value_01 for January,Value_02 for February etc.). I had created the Month lookup table and tried but didn't help.

The expected output:

Date	DB Name - Points Id	unitTarget
31-Dec-26	INSE-792	659.5999756
30-Dec-26	INSE-792	659.5999756
29-Dec-26	INSE-792	659.5999756

This above 659.599 is the corresponding target value of INSE-792 for month of December(Value_Type 12).

Can you please guide me on this ?

PFA sample file in below link:

Calculated column referencing other tables_total.pbix

Please let me know if you need further info.

Thanks in advance

@Ahmedx @grantsamborn @Greg_Deckler 

[Ahmedx · Accepted solution]

pls try this

https://1drv.ms/u/s!AiUZ0Ws7G26Rh2gsKGUJc5aLiucT?e=KtMubl

[sivarajan21]

Hi @Ahmedx ,

Brilliant and spot on!

Thats the expected solution and I will accept this as a solution.

Kudos to your quick response.

Once again many thanks to you and the community!

[sivarajan21]

Hi @Ahmedx ,

Apologise for the delay in response!

Brilliant, many thanks for wonderful solution.

Will get back on this after testing them😊

Thanks