Hi @yasbos
Yes, you can create the desired waterfall chart in Power BI based on your data, but you'll need to restructure it slightly to fit the requirements of a waterfall chart. Here's how you can do it:
Step 1: Understand Your Data Structure
Your data represents incidents with positive and negative counts:
- Positive Counts: New incidents added.
- Negative Counts: Incidents resolved or closed.
- Categories: Represent different stages or types (e.g., Older_Criticals, New_Criticals).
Step 2: Restructure Your Data
To create a waterfall chart, you need a dataset where each row represents a step in the waterfall with a category and a value. You may need to aggregate your data to summarize the counts per category and date.
Restructured Data Example:
| Start_Date |
Category |
Value |
AREA_ID |
SEVERITY_ID |
| 11/18/2024 |
Start Balance |
40 |
1 |
1 |
| 11/18/2024 |
New Incidents |
5 |
1 |
1 |
| 11/18/2024 |
Resolved Incidents |
-25 |
1 |
1 |
| 11/18/2024 |
End Balance |
20 |
1 |
1 |
| 11/25/2024 |
Start Balance |
20 |
1 |
1 |
| 11/25/2024 |
New Incidents |
5 |
1 |
1 |
| 11/25/2024 |
Resolved Incidents |
-25 |
1 |
1 |
| 11/25/2024 |
End Balance |
0 |
1 |
1 |
Notes:
- Start Balance: The number of incidents carried over from the previous period.
- New Incidents: Net new incidents (New_Criticals positive counts minus negative counts).
- Resolved Incidents: Incidents resolved (negative counts from Older_Criticals and New_Criticals).
- End Balance: Calculated as Start Balance + New Incidents + Resolved Incidents.
Step 3: Prepare the Data in Power BI
-
Import Your Data into Power BI.
-
Create a Calculated Table (if necessary) to aggregate and restructure your data:
WaterfallData =
VAR StartBalances =
SUMMARIZE(
FILTER('YourTable', 'YourTable'[CATEGORY] = "Older_Criticals"),
'YourTable'[Start_Date],
"Category", "Start Balance",
"Value", SUM('YourTable'[COUNT])
)
VAR NewIncidents =
SUMMARIZE(
FILTER('YourTable', 'YourTable'[CATEGORY] = "New_Criticals" && 'YourTable'[COUNT] > 0),
'YourTable'[Start_Date],
"Category", "New Incidents",
"Value", SUM('YourTable'[COUNT])
)
VAR ResolvedIncidents =
SUMMARIZE(
FILTER('YourTable', 'YourTable'[COUNT] < 0),
'YourTable'[Start_Date],
"Category", "Resolved Incidents",
"Value", SUM('YourTable'[COUNT])
)
RETURN
UNION(StartBalances, NewIncidents, ResolvedIncidents)
Calculate End Balance:
EndBalance =
CALCULATE(
SUM(WaterfallData[Value]),
FILTER(
ALL(WaterfallData),
WaterfallData[Start_Date] <= MAX(WaterfallData[Start_Date])
)
)
Step 4: Create the Waterfall Chart
-
Insert a Waterfall Chart Visual into your report.
-
Configure the Fields:
- Category: Add WaterfallData[Category].
- Y-Axis (Value): Add WaterfallData[Value].
- Breakdown (if needed): Add WaterfallData[Start_Date] to separate by date.
- Tooltips: Include AREA_ID, SEVERITY_ID as needed.
-
Set Up the Waterfall Steps:
- Starting Point: Ensure that "Start Balance" is set as the starting point.
- Increases and Decreases: "New Incidents" as increases, "Resolved Incidents" as decreases.
- Ending Point: "End Balance" can be set as the subtotal or final bar.
-
Customize the Visual:
- Data Colors: Assign specific colors to increases, decreases, and totals.
- Sorting: Sort the categories in the logical order of the steps.
Step 5: Handle Multiple Severities and Areas
- Filters or Slicers: Add slicers for SEVERITY_ID and AREA_ID to allow users to filter the chart.
- Dynamic Titles: Use measures to display the selected severity and area in the chart title.
Example Visual Flow
- Start Balance: Represents incidents carried over.
- New Incidents: Added to the start balance.
- Resolved Incidents: Subtracted from the total.
- End Balance: Shows the total incidents at the end of the period.
Additional Tips
- Data Validation: Ensure that your counts are accurate after restructuring.
- Date Handling: Verify that the dates are consistent and correct any typos.
- Dynamic Calculations: Use DAX measures to handle calculations if your data updates frequently.
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn|Twitter|Blog |YouTube
