Azure Application Insights Query - How to calculate percentage of total

Question

I'm trying to create a row in an output table that would calculate percentage of total items:

Something like this:
ITEM   |   COUNT   |   PERCENTAGE
item 1 |     4     |   80
item 2 |     1     |   20 

I can easily get a table with rows of ITEM and COUNT, but I can't figure out how to get total (5 in this case) as a number so I can calculate percentage in column %.

someTable
| where name == "Some Name"
| summarize COUNT = count() by ITEM = tostring( customDimensions.["SomePar"])
| project ITEM, COUNT, PERCENTAGE = (C/?)*100 

Any ideas? Thank you.

Answer 1

It's a bit messy to create a query like that.

I've done it bases on the customEvents table in AI. So take a look and see if you can adapt it to your specific situation.

You have to create a table that contains the total count of records, you then have to join this table. Since you can join only on a common column you need a column that has always the same value. I choose appName for that.

So the whole query looks like:

let totalEvents = customEvents
//  | where name contains "Opened form"
    | summarize count() by appName
    | project appName, count_ ;
customEvents
//  | where name contains "Opened form"
    | join kind=leftouter totalEvents  on appName
    | summarize count() by name, count_
    | project name, totalCount = count_ , itemCount = count_1,  percentage = (todouble(count_1) * 100 / todouble(count_))     

If you need a filter you have to apply it to both tables.

This outputs: