KQL - Group data sum

Question

I want to do group the data by department name column Department and compute the two columns- First column is the total sum of salary with compensation=1 and other column is total sum of salary with compensation=0.

datatable(Department:string, Emp_id:int, Compesation:int, Salary:int)
[
    'Automation', 34, 1, 5,
    'Automation', 35, 0, 7,
    'Dev', 67, 1, 6,
    'Dev', 68, 0, 5,
    'Dev', 69, 0, 6,
    'Testing', 91, 1, 4,
    'Testing', 92, 1, 1,
    'Testing', 93, 1, 9,
    'Testing', 94, 0, 2,
    'Testing', 95, 1, 4
];

I am expecting this output.

| Department | total_with_no_comp | total_with_compensation |
|------------|--------------------|-------------------------|
| Automation | 7                  | 5                       |
| Dev        | 11                 | 6                       |
| Testing    | 2                  | 18                      |

I tried grouping by Department and compensation columns, but I am getting 6 rows. But I want output in above format.

Answer 1

One way to solve this is using sumif() function instead of sum() while aggregating the value. Below is the code:

Code

table
|summarize total_with_no_comp = sumif(Salary,Compesation == 0),
total_with_compensation = sumif(Salary,Compesation == 1) by Department

sumif sums the value of salary within each department based on the condition given.

Demo