[go: up one dir, main page]
Scribd
Upload
10 views2 pages

stacked100 with extend

The document explains how to correctly generate a stacked 100% bar chart in Kusto Query Language (KQL) for students' exam results using `summarize` and `extend` instead of the `pivot` operator. It provides a sample query that aggregates passed and failed exam counts, handles missing values, calculates percentages, and renders the chart. The expected output includes the X-axis as StudentId, Y-axis as percentage, and a legend for passed and failed segments.

Uploaded by

saiakkina
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
10 views2 pages

stacked100 with extend

The document explains how to correctly generate a stacked 100% bar chart in Kusto Query Language (KQL) for students' exam results using `summarize` and `extend` instead of the `pivot` operator. It provides a sample query that aggregates passed and failed exam counts, handles missing values, calculates percentages, and renders the chart. The expected output includes the X-axis as StudentId, Y-axis as percentage, and a legend for passed and failed segments.

Uploaded by

saiakkina
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 2

You're right!

The `pivot` operator in Kusto does not work the way SQL pivoting
does. Instead, we need to **use `summarize` with `extend` or `make-series` to
reshape the data manually.**

Here’s the **correct Kusto Query Language (KQL) approach** to generate a **stacked
100% bar chart** for students' passed and failed exam counts:

---

### **📌 Corrected KQL Query**


```kql
let StudentExams = datatable(StudentId: string, ExamStatus: string, ExamCount: int)
[
"A", "Passed", 5,
"B", "Failed", 3,
"C", "Passed", 2,
"C", "Failed", 1,
"D", "Passed", 4,
"D", "Failed", 2
];

StudentExams
| summarize Passed = sumif(ExamCount, ExamStatus == "Passed"),
Failed = sumif(ExamCount, ExamStatus == "Failed")
by StudentId
| extend Passed = coalesce(Passed, 0), Failed = coalesce(Failed, 0) // Handle
missing values
| extend TotalExams = Passed + Failed
| extend PassedPct = todouble(Passed) / TotalExams * 100,
FailedPct = todouble(Failed) / TotalExams * 100
| project StudentId, PassedPct, FailedPct
| render barchart kind=stacked100
```

---

### **📌 Explanation of Fixes**


1. **Fixed Pivoting Issue**
- Instead of `pivot`, we use `summarize` with `sumif()` to **conditionally
aggregate** values for `"Passed"` and `"Failed"`.

2. **Handled Missing Values**


- `coalesce(Passed, 0)` ensures students with **only "Failed"** get `0` for
`"Passed"` and vice versa.

3. **Calculated Percentage Values**


- Converted counts into **percentages** for a **stacked 100% bar chart**.

4. **Rendered as a Stacked Bar Chart**


- `| render barchart kind=stacked100` makes sure each bar **adds up to 100%**.

---

### **📌 Expected Output**


✅ **X-Axis** → `StudentId`
✅ **Y-Axis** → `Percentage (%)`
✅ **Legend** → `"Passed"` and `"Failed"` segments per student

Would you like to customize it further, maybe adding a date filter or dynamic
parameters? 😊

You might also like