How to use custom column in power query using a measure logic.

[Rockz]

Dear Champions,

Iam using a measure as below

VAR D7 = CALCULATE(SUM(histories[actual]), histories[name] = "Total Cost")*-1

VAR E7 = CALCULATE(SUM(histories[commitment]), histories[name] = "Total Cost")*-1

VAR C7 = CALCULATE(SUM(histories[total]), histories[name] = "TotalCost")*-1

RETURN D7+E7-C7

I want same logic to be implemented in power query custom column. I tried in all the ways but not able to achieve it, please help me to implement and achieve this!!

Thanks in advance!

[v-tianyich-msft · Accepted solution]

Hi @Rockz ,

There seems to be a slight problem with the data you provided, I created simple samples and you can check the results below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJRMgWTFmCyJL8kMUchOb+4RClWJ1rJCCxoAiYtwaRvYgVC2hgsZIREIuuPBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Actual = _t, Commitment = _t, total = _t, name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Actual", Int64.Type}, {"Commitment", Int64.Type}, {"total", Int64.Type}, {"name", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom.1", each if [name] = "total cost" then [Actual] + [Commitment] - [total] else null),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom.1", "Final"}})
in
    #"Renamed Columns"

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[ROCKZZ]

Dear Tom,

This is the sample data and your assumption is correct. 

Actual	Commitment	total	Date	name
1.45	11.12	13457	10-05-2024	Total cost
1.33.33.335		90.881	11-05-2024	Miniam cost
			12-05-2024	Max cost
	20.019		13-05-2024	Total cost
-16.673			14-05-2024	avg cost
11.111	2239	2234	15-05-2024	Total cost
2.88.999	1.234	13456	16-05-2024	minaiml cost
34.44.44.456	42.098	-9173	17-05-2024	max cost
19.17.283	3.19.982	12.340	18-05-2024	Total cost
	11.223	1.29.083	19-05-2024	avg cost
19.90.183	9.081	10.973	20-05-2024	Total cost
1.09.873	19.082	1.39.083	21-05-2024	cost
33.330	2.34.59.081	1.50.097	22-05-2024	Total cost
134672	91.819	18.903	23-05-2024	avg cost
1.23.456	10.982	23.00.982	24-05-2024	max cost
2.001	19.083	1.097	25-05-2024	minimal cost
13.00.912	2.097	2.22.356	26-05-2024	Total cost
2.07.783	23.908	23.990	27-05-2024	Total cost
2.22.029	24	22.99.817	28-05-2024	Total cost

Thanks for helping!

[TomMartens]

Hey @Rockz ,

Please provide sample data that represents your table "histories." Make sure that the sample data also contains a column showing the expected result.

I assume you want the SUMmed value (D7+E7-C7) repeats for each of the rows, is this assumption correct?

Regards,

Tom