Pivoting columns in Power BI - Dashboard Guide

Dashboard Mode - Pivoting columns

Business Question

How can we transform sales data from a list format into a summary table that shows total sales per product for each month?

Sample Data

OrderID	Product	Month	Sales
1	Apples	Jan	100
2	Apples	Feb	150
3	Bananas	Jan	200
4	Bananas	Feb	180
5	Cherries	Jan	120
6	Cherries	Feb	130

Dashboard Components

• Pivot Table Visual: Shows total sales by Product and Month.
  Formula: Use Power BI's Matrix visual with Product in Rows, Month in Columns, and Sales as Values aggregated by SUM.
  Result example:
  

  Product	Jan	Feb
  Apples	100	150
  Bananas	200	180
  Cherries	120	130

• Total Sales Card: Shows total sales for all products and months.
  DAX Measure:
  Total Sales = SUM(SalesData[Sales])
  Result: 880
• Sales by Product Bar Chart: Displays total sales per product.
  DAX Measure:
  Sales by Product = CALCULATE(SUM(SalesData[Sales]))
  Visual: Bar chart with Product on X-axis and Sales on Y-axis.
  Values:
  Apples: 250, Bananas: 380, Cherries: 250

Dashboard Layout

+----------------------+----------------------+
|      Total Sales      |   Sales by Product   |
|        (Card)         |      (Bar Chart)     |
+----------------------+----------------------+
|                  Pivot Table                 |
|               (Matrix Visual)                |
+---------------------------------------------+

Interactivity

A slicer for Month allows filtering the data. When a month is selected, the Pivot Table, Total Sales Card, and Sales by Product Bar Chart update to show data only for that month.

Example: Selecting 'Jan' filters all visuals to show sales only for January.

Self Check

If you add a filter selecting Month = Feb, which components update and what will the Total Sales Card show?

Answer: All components update. The Total Sales Card will show 460 (150 + 180 + 130).

Key Result

A dashboard showing total sales by product and month using a pivot table, total sales card, and bar chart with interactive month filtering.