Calculated Net Column Help

[Anonymous]

I'm looking to achieve the Table 1 Requrement in the attached screenshot for my Table 1 dataset. Table 1 displays only one section of the dataset, however, the full dataset has multiple periods, fiscal years, and RA numbers. My requirement is to get table 1 data into a format that nets the values of regular pay and transfer to capital: regular and overtime pay and transfer to capital: overtime, so that it is matched up with table 2's values (i.e. if you add up all the regular pay values in table 2 it will equal table 1's regular pay + ransfer to capital: regular). How can I create this, essentially the Table 1 Requirement to the right.

[BA_Pete]

Hi @Anonymous ,

Not sure if I'm oversimplifying your requirements, but a couple of simple measures would do this:

Net: Regular Pay =
VAR pay =
CALCULATE(
    SUM(table[Value]),
    table[Financial Account] = "Regular Pay"
)
VAR capTransfer =
CALCULATE(
    SUM(table[Value]),
    table[Financial Account] = "Transfer to Capital: Regular"
)
RETURN
pay + capTransfer

Replace the relevant field names and values for your overtime measure.

Pete

[Anonymous]

@BA_Pete, this gets me the values, but is there a way I could replace out the old values (Regular Pay and Transfer to Capital: Regular) in the Power Query? I do not want a seperate row with Net: Regular Pay, I'd like to have it be part of the Financial Account column like the rghtmost table in the screenshot. Is this acomplishable? 

[BA_Pete · Accepted solution]

@Anonymous ,

You can do that in Power Query as follows, but it's not the ideal way to manage your data as you will lose the original detail around Pay and Capital Transfers:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hVBLC8IwDP4rpecpfViZ3sSzKCpexg5hxq1QH3TZwH9vhQ0qWL2EPL5HkqLgRgue8T3WnQPPdvAMlVQhKKHek1VFHbiQ6JmW82mueZmNrG2PnuwVf9GkEkJEnKOHW3tBz+jO1vCwBG7JBvuUxkQGZyP/qYzrJGX05yonbGzlkvAIuQFCb4d7vkCNWMRvoQaTt8S4jlp7RnZA39sK2+QLTR4aZfkC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"RA Number" = _t, #"Financial Account" = _t, Period = _t, #"Fiscal Year" = _t, Type = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", type number}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","Regular Pay","Net: Regular Pay",Replacer.ReplaceText,{"Financial Account"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Transfer to Capital: Regular","Net: Regular Pay",Replacer.ReplaceText,{"Financial Account"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Overtime Pay","Net: Overtime Pay",Replacer.ReplaceText,{"Financial Account"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Transfer to Capital: Overtime","Net: Overtime Pay",Replacer.ReplaceText,{"Financial Account"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value3", {"RA Number", "Financial Account", "Period", "Fiscal Year", "Type"}, {{"Value", each List.Sum([Value]), type number}})
in
    #"Grouped Rows"

Pete

[Greg_Deckler]

@Anonymous - Are those two tables related and if so, how? Can you post that data as text in a table?

[Anonymous]

@Greg_Deckler, they are related, as each has information on each RA (department) for each month and fiscal year. Table 1 is actual financial information from our interal data base, the Table 2 budget financial information is data from our budgeting software (in table 2 it shows "actual" in this instance instead of "budget" becasue I exported both budget and actual in the budgeting software to ensure the data matched for actuals in table 1). 

Table 1

RA Number	Financial Account	Period	Fiscal Year	Type	Value
530	Regular Pay	12	2020	Actual	$  34,316.83
530	Overtime Pay	12	2020	Actual	$                -
530	Transfer to Capital: Regular	12	2020	Actual	$      (116.51)
530	Transfer to Capital: Overtime	12	2020	Actual	$                -
530	Vehicle	12	2020	Actual	$                -
530	Material	12	2020	Actual	$       509.00
530	Other	12	2020	Actual	$                -
530	Outside Services	12	2020	Actual	$  15,820.00

Table 2

RA Number	Financial Account	Period	Fiscal Year	Type	Value	Short Fin Account
530	LABOR_ALL	12	2020	Actual	$       244.21	Regular Pay
530	LABOR_ALL	12	2020	Actual	$       131.48	Regular Pay
530	LABOR_ALL	12	2020	Actual	$    1,700.96	Regular Pay
530	LABOR_ALL	12	2020	Actual	$       295.99	Regular Pay
530	LABOR_ALL	12	2020	Actual	$  14,619.67	Regular Pay
530	LABOR_ALL	12	2020	Actual	$  (6,812.80)	Regular Pay
530	LABOR_ALL	12	2020	Actual	$       197.22	Regular Pay
530	LABOR_ALL	12	2020	Actual	$    6,812.80	Regular Pay
530	LABOR_ALL	12	2020	Actual	$       837.40	Regular Pay
530	LABOR_ALL	12	2020	Actual	$       683.92	Regular Pay
530	LABOR_ALL	12	2020	Actual	$  15,489.47	Regular Pay
530	OUT_SERV	12	2020	Actual	$       400.00	Outside Services
530	OUT_SERV	12	2020	Actual	$    7,000.00	Outside Services
530	OUT_SERV	12	2020	Actual	$    7,000.00	Outside Services
530	OUT_SERV	12	2020	Actual	$    1,420.00	Outside Services
530	MATERIALS	12	2020	Actual	$       275.00	Material
530	MATERIALS	12	2020	Actual	$          70.00	Material
530	MATERIALS	12	2020	Actual	$          39.00	Material
530	MATERIALS	12	2020	Actual	$       125.00	Material