Forum Discussion
Create calculated columns by summing values in another table
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.
7 Replies
pls try this
Hi Ahmedx ,
Apologise for the delay in response!
Brilliant, many thanks for wonderful solution.
Will get back on this after testing them😊
Thanks
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
pls try this
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
