Kusto | add column to show percentages of total

user_5835002 0
Viewed 5974

I have seen several other questions similar to this but each is slightly different and none of them provide an answer I was able to adapt to my situation. I have a table like this:

let T = 
    datatable(Val1:string, Val2:string, Val3:bool)
    [
        '', 'false', 'false',
        'Yes', 'false', 'true',
        'No', 'false', 'false',
        'Yes', 'false', 'false'
    ]
;

I want to only get results where Val3 is false, summarize count by Val1 & Val2, then extend a column to show the percentage of the whole on each row. I have tried doing this:

T
| where Val3 == "false"
| summarize Count = count() by Val1, Val2
| let Total = sum(Count)
| extend Percentage = round(100.0 * Count/Total, 0)

Which throws an error "A recognition error occurred. Token: let" And I've tried many variations such as this:

T
| where Val3 == "false"
| summarize Count = count() by Val1, Val2
| extend Total = sum(Count)
| extend Percentage = round(100.0 * Count/Total, 0)

Which throws an error "Function 'sum' cannot be invoked in current context". If I change extend Total to summarize Total then that line works, but it throws an error about not recognizing Count on the next line. If I add Count on the summarize line like this:

| summarize Total = sum(Count), Count

Then I get an error "Non valid aggregation function is used after summarize". This is the output I'm going for:

expected results

It seems like this is a lot more difficult than it should be. What am I missing?

kql azure-data-explorer
1 Answers

You can calculate the percentage using a sub-query to get the total, passed into toscalar(). In the examples below, I've also used the as operator.

datatable(Val1:string, Val2:string, Val3:bool)
[
    '', 'false', 'false',
    'Yes', 'false', 'true',
    'No', 'false', 'false',
    'Yes', 'false', 'false'
]
| where Val3 == "false"
| as T
| summarize Count = count() by Val1, Val2
| extend Percentage = round(100.0 * Count / toscalar(T | count), 2)

or, following the same concept - here's an alternative:

datatable(Val1:string, Val2:string, Val3:bool)
[
    '', 'false', 'false',
    'Yes', 'false', 'true',
    'No', 'false', 'false',
    'Yes', 'false', 'false'
]
| where Val3 == "false"
| summarize Count = count() by Val1, Val2
| as T
| extend Percentage = round(100.0 * Count / toscalar(T | summarize sum(Count)), 2)
user_78618070
Related