Splitting and merging columns in Power BI - Dashboard Guide

Dashboard Mode - Splitting and merging columns

Business Question

How can we split full customer names into first and last names, and merge city and state into a single location column for better analysis?

Sample Data

CustomerID	FullName	City	State	Sales
101	John Smith	Seattle	WA	250
102	Mary Johnson	Portland	OR	300
103	James Lee	San Francisco	CA	400
104	Linda Davis	Denver	CO	150
105	Michael Brown	Seattle	WA	350

Dashboard Components

• KPI Card: Total Sales with formula =SUM(Sales) showing 1450
• Table: Customer details with split columns FirstName and LastName created by splitting FullName at the space.
• Table: Location column created by merging City and State with a comma and space.
• Bar Chart: Sales by Location showing total sales per city-state combination.

Formulas / Expressions

// Split FullName into FirstName
FirstName = LEFT(Customer[FullName], FIND(" ", Customer[FullName]) - 1)

// Split FullName into LastName
LastName = RIGHT(Customer[FullName], LEN(Customer[FullName]) - FIND(" ", Customer[FullName]))

// Merge City and State into Location
Location = Customer[City] & ", " & Customer[State]

// Total Sales measure
Total Sales = SUM(Customer[Sales])
  

Dashboard Layout

+----------------------+-------------------------+
|      Total Sales      |      Sales by Location   |
|       (KPI Card)      |       (Bar Chart)        |
+----------------------+-------------------------+
|        Customer Details Table (with split names and merged location)       |
+-----------------------------------------------------------------------------+
  

Interactivity

A slicer on Location allows filtering the bar chart and the customer details table to show only customers from the selected city-state. Selecting a location updates the total sales KPI to reflect sales only from that location.

Self Check

If you add a filter for Location = "Seattle, WA", which components update and what will the total sales show?

• The bar chart will show sales only for Seattle, WA.
• The customer details table will list only customers from Seattle, WA.
• The total sales KPI will update to show 600 (250 + 350).

Key Result

Dashboard showing customer sales with split first and last names and merged city-state location for filtering and analysis.