Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Calculated Net Column Help

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.

 

Calculated Column.PNG

1 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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

5 REPLIES 5
BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Anonymous
Not applicable

@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? 

@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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Greg_Deckler
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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 NumberFinancial AccountPeriodFiscal YearType  Value 
530Regular Pay122020Actual $  34,316.83
530Overtime Pay122020Actual $                -  
530Transfer to Capital: Regular122020Actual $      (116.51)
530Transfer to Capital: Overtime122020Actual $                -  
530Vehicle122020Actual $                -  
530Material122020Actual $       509.00
530Other122020Actual $                -  
530Outside Services122020Actual $  15,820.00

 

Table 2

RA NumberFinancial AccountPeriodFiscal YearType  Value Short Fin Account
530LABOR_ALL122020Actual $       244.21Regular Pay
530LABOR_ALL122020Actual $       131.48Regular Pay
530LABOR_ALL122020Actual $    1,700.96Regular Pay
530LABOR_ALL122020Actual $       295.99Regular Pay
530LABOR_ALL122020Actual $  14,619.67Regular Pay
530LABOR_ALL122020Actual $  (6,812.80)Regular Pay
530LABOR_ALL122020Actual $       197.22Regular Pay
530LABOR_ALL122020Actual $    6,812.80Regular Pay
530LABOR_ALL122020Actual $       837.40Regular Pay
530LABOR_ALL122020Actual $       683.92Regular Pay
530LABOR_ALL122020Actual $  15,489.47Regular Pay
530OUT_SERV122020Actual $       400.00Outside Services
530OUT_SERV122020Actual $    7,000.00Outside Services
530OUT_SERV122020Actual $    7,000.00Outside Services
530OUT_SERV122020Actual $    1,420.00Outside Services
530MATERIALS122020Actual $       275.00Material
530MATERIALS122020Actual $          70.00Material
530MATERIALS122020Actual $          39.00Material
530MATERIALS122020Actual $       125.00Material

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.