Build an Electric Vehicle (EV) Charging & Fleet Performance Dashboard in Power BI

on
Build an Electric Vehicle (EV) Charging & Fleet Performance Dashboard in Power BI – Step-by-Step Tutorial

⚡ Build an EV Charging & Fleet Performance Dashboard in Power BI

End-to-end tutorial: data model → DAX → visuals → UX → optimization → publishing. Perfect for fleet operators, charging networks, and sustainability teams.

Power BI EV Analytics Charging Sessions Battery Health CO₂ Savings

Table of Contents

  1. Data Model & Sources
  2. Power Query Transformations
  3. Core DAX Measures
  4. Visual Layout & Insights
  5. Design & UX
  6. Performance Optimization
  7. Publishing & Refresh
  8. FAQ & Extensions

Why an EV Dashboard?

EV programs generate rich data: charging sessions, energy (kWh), tariffs, idle time, trips, battery temps/SoH, and station uptime. Executives want simple KPIs; operators need granular diagnostics. This tutorial shows how to build a Power BI report that serves both—clean model, robust DAX, fast visuals, and a polished UX.

1) Data Model & Sources

Fact Tables

  • ChargingSessions: SessionID, VehicleID, StationID, StartTime, EndTime, kWh, EnergyDelivered, Cost, Tariff, PeakFlag, IdleMinutes.
  • Trips: TripID, VehicleID, StartTime, EndTime, DistanceKM, AvgSpeed, EnergyUsed_kWh.
  • EnergyPrices: DateTime, Region, TariffPlan, PricePerkWh.

Dimension Tables

  • Vehicle: VehicleID, Make, Model, BatteryCapacity_kWh, VehicleType (2W/4W/Bus), InServiceDate.
  • ChargingStation: StationID, StationName, Latitude, Longitude, ConnectorType, MaxPower_kW, Region.
  • Date: Date, Year, Month, Day, Week, Quarter, IsWeekend, Hour (if at hourly grain).

Relationships

  • ChargingSessions[VehicleID] → Vehicle[VehicleID] (Many-to-One)
  • ChargingSessions[StationID] → ChargingStation[StationID]
  • ChargingSessions[StartTime] → Date[Date] (via date part or a separate datetime dimension for hour-level analysis)
  • Trips[VehicleID] → Vehicle[VehicleID]
  • EnergyPrices[DateTime] ↔ Date[Date]/Hour (align grain!)
Tip: Keep fact tables at consistent grain. If ChargingSessions is session-level and EnergyPrices is hourly, aggregate prices to session-level (session’s average price) or expand sessions to hourly bins—pick one and stay consistent.

2) Power Query Transformations (ETL)

ChargingSessions steps

  1. Split times → StartDate, StartHour, EndDate, EndHour
  2. Create DurationMinutes = Duration.TotalMinutes(EndTime - StartTime)
  3. kWh = EnergyDelivered (rename/standardize)
  4. CostPerkWh = try Cost / kWh otherwise null
  5. Flag peak/off-peak via hour band; PeakFlag
  6. Remove null/zero-length sessions; cap outliers (e.g., > 24h)

Trips steps

  1. Ensure DistanceKM numeric; remove negatives
  2. EnergyPerKM = try EnergyUsed_kWh / DistanceKM otherwise null
  3. Compute trip date, hour, weekday/weekend

Date table

// DAX (Modeling > New Table)
Date = 
ADDCOLUMNS(
  CALENDAR(DATE(2023,1,1), DATE(2026,12,31)),
  "Year", YEAR([Date]),
  "Month", FORMAT([Date], "MMM"),
  "MonthNum", MONTH([Date]),
  "Quarter", "Q" & FORMAT([Date], "Q"),
  "Weekday", FORMAT([Date], "ddd"),
  "IsWeekend", IF(WEEKDAY([Date],2)>=6, TRUE(), FALSE())
)
Pro: Keep pricing and session data separate in Power Query, then join by Date/Hour or compute weighted averages in DAX. This isolates messy tariff logic from the clean session fact.

3) Core DAX Measures (KPIs)

Energy & Sessions

Total kWh = SUM(ChargingSessions[kWh])

Total Sessions = COUNTROWS(ChargingSessions)

Avg kWh per Session =
DIVIDE([Total kWh], [Total Sessions])

Avg Session Duration (min) =
AVERAGE(ChargingSessions[DurationMinutes])

Cost & Efficiency

Total Cost = SUM(ChargingSessions[Cost])

Effective Cost per kWh =
DIVIDE([Total Cost], [Total kWh])

Trips Distance (KM) = SUM(Trips[DistanceKM])

Energy per KM (kWh/km) =
DIVIDE(SUM(Trips[EnergyUsed_kWh]), [Trips Distance (KM)])

Cost per KM =
DIVIDE([Total Cost], [Trips Distance (KM)])

Fleet & Stations

Total Vehicles = DISTINCTCOUNT(Vehicle[VehicleID])

Active Vehicles =
CALCULATE(
  DISTINCTCOUNT(Vehicle[VehicleID]),
  FILTER(Vehicle, Vehicle[InServiceDate] <= MAX(Date[Date]))
)

Utilization % =
DIVIDE([Active Vehicles], [Total Vehicles])

Top Station kWh =
VAR t = SUMMARIZE(ChargingStation, ChargingStation[StationID], "k", CALCULATE([Total kWh]))
RETURN MAXX(t, [k])

Sustainability

-- Set a parameter/table for emission factor (kg CO2e per kWh from grid)
-- Example: 0.7 kg CO2e/kWh (adjust for your grid mix)
CO2 Factor (kg per kWh) = SELECTEDVALUE(Settings[CO2_kg_per_kWh], 0.7)

CO2 Saved (kg) =
[Total kWh] * [CO2 Factor (kg per kWh)]

CO2 Saved (tonnes) =
DIVIDE([CO2 Saved (kg)], 1000)
Note: If you compare EV vs ICE baseline, add a separate measure for Avoided CO₂ using ICE fuel economy & emission factors, then subtract EV emissions (grid or renewable).

Time Intelligence

Total kWh MTD = CALCULATE([Total kWh], DATESMTD(Date[Date]))
Total kWh YTD = CALCULATE([Total kWh], DATESYTD(Date[Date]))

Sessions WoW % =
VAR Prev = CALCULATE([Total Sessions], DATEADD(Date[Date], -7, DAY))
RETURN DIVIDE([Total Sessions] - Prev, Prev)

4) Visual Layout & Insights

KPI Card Total kWh – current filter context
KPI Card Total Sessions – with WoW/YoY
KPI Card Cost per kWh – effective blended

Page 1 – Fleet Overview (Exec)

  • Line chart: Total kWh by Month (trend)
  • Clustered bar: Top 10 Vehicles by kWh (or by Cost)
  • Donut: Energy by Vehicle Type (2W / 4W / Bus)
  • KPI strip: Cost per km, Energy per km, Utilization %
  • Slicer panel: Date, Region, Vehicle Type, Connector Type, Peak/Off-Peak

Page 2 – Charging Insights (Operations)

  • Column chart: Sessions by Hour of Day (peak vs off-peak)
  • Table: Station Utilization – Sessions, kWh, IdleMinutes, Avg Duration
  • Map: ChargingStation locations colored by Total kWh or Utilization
  • Scatter: Session Duration vs kWh (outlier detection)
  • Decomposition tree (optional): Drivers of Cost per kWh (region, time band, tariff)

Page 3 – Sustainability (ESG)

  • KPIs: CO₂ Saved (t), Renewable Share %, Grid Emissions Factor
  • Area chart: CO₂ Saved over time
  • Bar: CO₂ Saved by Region / Vehicle Type
  • What-If parameter: Renewable energy % slider (simulate PPA/solar impact)
Pro: Use Field Parameters to let users toggle between kWh, Sessions, Cost, or Cost/kWh in the same visual without duplicating charts.

5) Design & UX

  • Theme: EV palette — charcoal background, cyan & lime accents for KPIs; consistent fonts.
  • Layout: Three-row grid (KPIs → trends → detail tables). Keep slicers on left collapsible pane.
  • Tooltips: Build a Tooltip page with session summary (kWh, duration, cost, idle) and bind to map/bars.
  • Drill-through: From Vehicle → “Vehicle Detail” page (charging history, trips, cost timeline).
  • Conditional formatting: Red for high idle or high cost/kWh; green for improved efficiency.
Accessibility: Provide data labels, 4.5:1 contrast, keyboard-navigable slicers. Add alt text for images when exporting.

6) Performance Optimization

  • Grain strategy: Keep raw sessions in Import mode; pre-aggregate to daily/hourly summary tables for heavy visuals.
  • Measures > Calculated columns: Prefer measures for dynamic calcs; store only required columns.
  • Directional filters: Single-direction relationships unless a bi-directional path is absolutely necessary.
  • Sparse visuals: Limit visuals per page (< 8) to reduce DAX query fan-out.
  • Composite model (optional): Import historic data, DirectQuery for last 7-30 days of high-frequency logs.
Avoid: Many-to-many relationships on core facts, implicit time intelligence on DateTime at minute grain, SELECTCOLUMNS in heavy loops.

7) Publishing, Refresh & Governance

  • Parameters: Region lists, CO₂ factor, tariff plans as parameter tables (manageable by business users).
  • Gateways & Refresh: Schedule incremental refresh (partition by Date) for sessions and trips.
  • RLS (Row-Level Security): Restrict by Region or FleetOwner on Vehicle and ChargingStation.
  • App: Bundle pages into a Power BI App with clear navigation (Overview, Charging, Sustainability, Detail).
  • Versioning: Keep a change log: measure updates, model changes, theme tweaks.

FAQ & Extensions

How do I model tariffs that change by hour/region?

Create an EnergyPrices table keyed by Date+Hour+Region. Either:

  1. Join to sessions via hour buckets and compute WeightedAvgPrice by session duration; or
  2. Pre-expand sessions to hourly bins in Power Query and merge prices (simpler DAX, larger model).

Can I forecast charging demand?

Yes—use Power BI’s built-in forecast on monthly kWh, or bring a Python/R script for ARIMA/Prophet. Keep it on a separate “Forecast” page.

Battery State-of-Health (SoH)?

If you have SoH/temperature telemetry, add a BatteryHealth table (DateTime, VehicleID, SoH%, Temp). Create measures for Avg SoH and Degradation per 10k km, and chart over time by vehicle cohort.

Renewables vs grid mix?

Add a RenewableShare dimension (by region or site). Create a What-If parameter to simulate higher renewable adoption and recompute CO₂ Saved in measures.

You built it! You now have an executive EV overview, operational charging insights, and ESG tracking—backed by a clean model and fast DAX. Tweak the theme and add your logo to ship it.

Comments

Post a Comment