How to create a spending trend dashboard in Power BI
Introduction
This guide walks you through the process of creating a spending trend dashboard using Power BI. This approach will provide you with a comprehensive view of spending patterns over time, enabling better financial management and strategic planning.
The full video for this step-by-step approach is provided below:
The procedure below assumes a knowledge of data preparation using Power Query. However, beginners can refer to the following lessons on how to prepare data with DAX functions for a Power dashboard.
Get the file for this project here and follow along with the procedure.
1. Create dashboard placeholders
Open Microsoft Power Point
- Select the Insert menu – shapes – choose “Rectangle”.
- Drag to draw a rectangle.
- Right-click on the rectangle – choose format shape – on the format shape pane, choose fill – solid fill. Choose color as white. Then choose line – no line.
- Switch to the shapes and size properties, enter 1.85 cm as height of the rectangle & 16.2 cm as width of the rectangle. Choose effects – soft edges (the second option).
Position and Duplicate Shapes
- Adjust the rectangle and position it at the top left-hand corner.
- Copy and paste the rectangle you created. Position the copied rectangle to the top right-hand corner.
- Copy and paste any of the rectangles – resize the copied rectangle to a height of 6.7 cm and width of 16.2 cm. Copy and paste this rectangle to the right.
- Create 2 additional and identical rectangles with a height of 8.87cm and width of 16.2 cm. Adjust and position these below the previous rectangles.
Save the PowerPoint File
- Save the PPT file as ‘Cor Exp’ and choose “Scalable Vector Graphics Format (*.svg)” as the file format.
Create Power BI visualisations (KPI / Metrics)
Note: Do not worry about the position of your visuals because you will eventually put them in placeholders later.
Create Line and Stacked Column chart
- Select ‘Line and Stacked Column chart ‘ from the visualisation pane.
- Choose or drag and drop “IT Area” and “IT Sub Area” into the X-axis. Choose or drag and drop “Actual” into the Column y-axis. Choose or drag and drop “Plan” into the Line y-axis.
- Choose Format Visual – Visual – Y-axis – toggle values off.
- Lines – Line style – Dotted – Join type – Round – Width – 2 – Interpolation Type – Step – Step Position – Centre – Color – #EF008C Theme color 5.
- Choose Data Labels – Toggle Background Off. Choose Options – Position (column) – Inside Base.
- Format Visual – Choose General – Title – Font – Segoe UI )Bold) , 10.
Create Stacked Area Chart
- Select ‘ Stacked Area Chart ‘ from the visualisation pane.
- Choose or drag and drop “Period”, “Actual” and “Business Area” into the X-Axis, Y-Axis and Legend Fields respectively.
- Choose format – Visual – Legend – Text – Font – Segoe UI, 8.
- Toggle Title Off.
- Lines – Line style – Solid – Join type – Round – Width – 2 – Interpolation Type – Linear.
- Toggle Total Labels On.
- Format Visual – Choose General – Title – Font – Segoe UI (Bold) , 10.
Create Decomposition Tree
- Choose Decomposition Tree from the Visualisations pane.
- Choose or drag and drop Var Plan into Analyse Field.
- Choose or drag and drop IT Area, Period, Sales Region, IT Sub Area, Country/Region into Explain By Field.
- Expand the Decomposition Tree by clicking the “+” and choose IT Area (or any item of your choice) from the list. Click Infrastructure to expand and choose Period from the list.
Create Map
- Choose Map from the Visualisations pane.
- Choose or drag and drop Country/Reion into the Location field
- Choose or drag and drop Var Plan % into the Tooltip field.
- Choose Format Visual – Visual – Map Setting – Style – Grayscale
- Choose Bubbles – size – size – 70.
- Choose Bubbles – Colors – select Conditional formatting (fx) – Choose Gradient as Format style – Choose “What field should we base this on?” – Var Plan – Choose “minimum” #FFA600, Theme color 1. Choose “maximum” #13102, Theme color 3. Click OK.
- Format Visual – Choose General – Title – Font – Segoe UI (Bold) , 10.
Create Slicer
- Choose slicer – drag and drop or select Sales Region from the Field box from the Data Pane.
- Choose format visual – Visual – Slicer settings – options – choose dropdown.
- Choose values – Font, Segoe UI, 10, color – black.
- Choose Slicer Header and toggle Off.
Create Text Box
- Choose Insert menu.
- Select text box – drag & adjust the text box.
- Type in “Select Sales Region”.
- Select the text and choose font Segoe (bold), 10, and make the text italics.
- Adjust and position the text box close to the slicer.
Create Title
- Choose Insert menu – Element – text box.
- Drag and adjust the text box.
- Type in “SPEND TREND ANALYSIS”, select the text and make the font Segoe (Bold), 18, Center justification.
- Choose format text box – General – Effects – toggle background off.
Format Power BI Background
Customise Canvas Background
- On the Visualisation Pane choose Format – Canvas background – Colour – choose white, 20% darker.
- Choose Image and click on the small icon – browse to the ‘Spend Trend’ file and select open – Image Fit – select Fit – Transparency – 0.
Now Move Your Visuals into the Placeholders
Conclusion
In this guide, we explored how to create a spending trend dashboard in Power BI, starting with the design of dashboard placeholders in PowerPoint to ensure a visually appealing layout.
We then moved to Power BI to set up various visualisations, including line and stacked column charts, area charts, decomposition trees, and maps.
By integrating these visual elements, we effectively highlighted spending trends and patterns across different business areas and regions.
Finally, the inclusion of slicers and text boxes added interactivity and clarity to the dashboard.
