12

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.

Peter Bons
  • 26,826
  • 4
  • 50
  • 74
mpnap
  • 153
  • 1
  • 1
  • 8

4 Answers4

10

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:

enter image description here

Peter Bons
  • 26,826
  • 4
  • 50
  • 74
  • Awesome. Made it work exactly how I wanted. My "join" game is weak, so any attempts I had in that direction failed. Thank you. – mpnap Oct 14 '16 at 15:32
5

It is not even necessary to do a join or create a table containing your totals Just calculate your total and save it in a let like so.

let totalEvents = toscalar(customEvents
| where timestamp > "someDate"
    and name == "someEvent"
| summarize count());

then you can simply add a row to your next table, where you need the percentage calcualtion by doing:

| extend total = totalEvents

This will add a new column to your table filled with the total you calculated. After that you can calculate the percentages as described in the other two answers.

| extend percentages = todouble(count_)*100/todouble(total)

where count_ is the column created by your summarize count() which you presumably do before adding the percentages.

Hope this also helps someone.

Maverick1st
  • 3,774
  • 2
  • 34
  • 50
3

I think following is more intuitive. Just extend the set with a dummy property and do a join on that...

requests
| summarize count()
| extend a="b" 
| join (
    requests
    | summarize count() by name
    | extend a="b"    
) on a 
| project name, percentage = (todouble(count_1) * 100 / todouble(count_))
Nisheet Jain
  • 47
  • 1
  • 1
1

This might work too:

someTable
| summarize count() by item
| as T 
| extend percent = 100.0*count_/toscalar(T | summarize sum(count_)) 
| sort by percent desc 
| extend row_cumsum(percent)
Pradeep Vairamani
  • 4,004
  • 3
  • 36
  • 59