Subtracting two rows from same column with condition on different column in Power BI

Question

I am creating a table in Power Bi, where I have job level and for each job level I have two rows one for "Existing Employees" and second for "New Hires". Existing Employees and new hires have comp ratios. I want to create a new column which provides the difference between New Hire comp ratio and existing employees comp ratio for each job level. If result is negative then show blank else show the result.

Something like below:

Job Level   Employee Group  Comp Ratio  Difference
3            Existing             108%         -108 ( don't show this)
3            New Hire             0%
4            Existing             107%          3
4            New Hire             110%
5            Existing             104%         -1 (Don't show this)
5            New Hire             103%

Thanks, CSTech

screenshot below :

Answer 1

You can do this in Power Query M Code

• Enter the Power Query Editor Home => Transform Data
• Enter the Advanced Editor Home => Query => Advanced Editor
• In the editor delete, the last few lines starting with in ...
• Paste the code below after the new end of your code
• In the first line of newly pasted code, change #"Previous Step" to the name of the actual previous step in your code.

Algorithm

• Group into pairs by Job Level
• Add a column to each grouped sub table
  • if the Employee Group is New Hire then write a null
  • If the 2nd entry in the Comp Ratio column is greater than the first, then subtract and write the difference, otherwise write a 'null'

//Add this to your code
    #"Grouped Rows" = Table.Group(#"Previous Step", {"Job Level"}, {
        {"Difference", (t)=> Table.AddColumn(t,"Difference", each 
            if [Employee Group] = "New Hire" then null 
                else if t[Comp Ratio]{1} > t[Comp Ratio]{0}
                   then t[Comp Ratio]{1} - t[Comp Ratio]{0}
            else null,Percentage.Type), type table[Job Level=Int64.Type, Comp Ratio=Percentage.Type, Difference=Percentage.Type]}
            }),
    #"Expanded Difference" = Table.ExpandTableColumn(#"Grouped Rows", "Difference", 
         {"Comp Ratio", "Difference"})
in
    #"Expanded Difference"

Results with your data

M Code to reproduce the above table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlbSUXKtyCwuycxLBzINDSz0DAxUlWJ1IFJ+qeUKHplFqUCmAVzCBF2POYoUkh5DQ4QuU3RdJihSyLoMjCFSsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Job Level" = _t, #"Employee Group" = _t, #"Comp Ratio" = _t]),
    #"Previous Step" = Table.TransformColumnTypes(Source,{
        {"Job Level", Int64.Type}, 
        {"Employee Group", type text}, 
        {"Comp Ratio", Percentage.Type}}),

//Add this to your code
    #"Grouped Rows" = Table.Group(#"Previous Step", {"Job Level"}, {
        {"Difference", (t)=> Table.AddColumn(t,"Difference", each 
            if [Employee Group] = "New Hire" then null 
                else if t[Comp Ratio]{1} > t[Comp Ratio]{0}
                   then t[Comp Ratio]{1} - t[Comp Ratio]{0}
            else null,Percentage.Type), type table[Job Level=Int64.Type, Comp Ratio=Percentage.Type, Difference=Percentage.Type]}
            }),
    #"Expanded Difference" = Table.ExpandTableColumn(#"Grouped Rows", "Difference", 
         {"Comp Ratio", "Difference"})
in
    #"Expanded Difference"