Are you new to BI tools and wondering where to start? Your best bet is to search online for a handy tutorial that spares no details. Well, you got it! In this article, I’ll walk you through how I created a retail KPI report in Power BI.
What Is a KPI Report?
A key performance indicator report tracks business progress, showing how far you’ve come, where you stand now and what adjustments can help you win. KPIs are specific metrics chosen to track progress toward departmental and overall company goals.
Building these can seem challenging because metrics will differ depending on the unique priorities of each organization.
Beyond overall goals, KPIs are specific to different departments. Marketing KPIs will be different from sales KPIs, though they both contribute to overall company growth.
The key to building helpful KPI reports lies in understanding the specific objectives of your department. The better you understand those goals, the easier it’ll be to determine which KPIs matter the most and how to relate them best.
Here’s an account of my first-hand experience with KPI reporting.
Building the KPI Framework
I recently completed a guided Power BI project on Maven Analytics, building an interactive retail KPI (key performance indicators) report.
As part of the project, I was a data analyst for a toy store with multiple locations. The data included CSV files containing details of products, sales and store locations from January 2022 to September 2023.
I anticipated data modeling to be technical but found connecting tables seamless with Power BI’s intuitive interface.
Crafting custom metrics used to sound intimidating, but Power BI’s DAX formulas were surprisingly straightforward. With a few clicks, I created powerful calculations to unlock deeper insights from my retail data.
The result? An interactive retail KPI dashboard with the option to drill down into specific metrics and uncover hidden trends with just a click.
Here’s what I built.
And here’s how I did it.
Preparation involved downloading Power BI and deciding on the KPIs and data sources. It would eventually drive the profit and revenue figures.
After downloading Power BI, the first step was acquainting myself with the homepage and the Power Query editor.
The left pane on the Power BI homepage had the report, table, model and DAX view icons. The left pane of the Power Query Editor showed the source files once I loaded them.
For the KPIs, my usual suspects were total revenue and profit. The KPI report would need a date hierarchy to drill down into data, which I added in a subsequent step.
The tutorial included downloading sample data for calendars, products, stores and sales.
Here are the steps.
Gathering Data
1. On the homepage, navigate to Home > Queries and click Transform Data to open the Power Query Editor dialog box.
2. Click Text/CSV under Home > Query > New Source. In the File Manager, select the folder containing the CSV files.
3. Pull data from each file in turn. Once done, you should have data from the calendar, products, sales and stores files in Power BI.
Power Query automatically detects the data type as you import it. It lets you choose whether you want it to detect the data types for the first 200 rows, the entire dataset, or not at all.
However, Power BI couldn’t detect the dates in the calendar CSV and I needed to change it from text to date type.
Changing the Data Type to Date
Power BI requires a two-step process to transform text data into date type. First, we should promote the first row to the header as the word Date is not a date (!). The header showed Column1 when it should show Date.
1. Click the calendar grid icon next to the Column1 header and select Use First Row as Headers from the menu. Remember, this step is crucial! If you skip it and try to change the data type directly, Power BI will show an error message.
2. Click the ABC icon next to Column1 and select Change Type with Locale to open another dialog box. Choose Date as the data type and English (United States) as the locale (assuming your dates follow the American format).
The Date header will display a calendar icon, and the dates will shift neatly to the right.
Confirm the data types are correct before building your KPI report.
Adding Date Columns
Remember the date hierarchy for the trend axis? Here’s how to add it to the calendar table.
Add the start-of-month and start-of-week columns to set a time-based hierarchy.
1. Go to Add Columns > Date > Month in the ribbon and select Start of Month.
2. Similarly, add the Start of Week option.
Changing the Data Type to Currency
Upon importing the products file, I noticed the product_cost and product_price columns displayed as text. Here’s how to change the data type to currency.
1. Head to the Transform tab in the ribbon.
2. Click Extract > Text After Delimiter.
3. Enter the dollar sign ($) as the delimiter. Power BI removes the dollar sign, and the
numbers move to the right.
While going through the columns of data, identify the primary keys for the data model, in this case, date, product_id and store_id.
Interpreting the Data
Once the data is in Power BI, check it for accuracy and uniqueness.
In a 1:1 interaction, Nate LaFerle, Principal at Remisphere Digital, talked to me about the importance of data quality management.
Successful BI projects include a significant data quality component. Organizations often overlook the business processes that contribute to bad data quality and instead focus on cleaning up bad data once it’s already in their systems.
“The old adage ‘garbage in, garbage out’ has never been more true, and this will become increasingly important as organizations move to leverage these datasets for AI/ML training.”
1. Click on each column header to check for errors, null values and duplicates. You can glean a lot of information from the column statistics and the bar chart.
See the image below. Can you find out how many stores there are?
Click on the column header to view the column stats and value distribution.
2. Select the products table to learn about the items with the minimum and maximum prices.
Click on the bars in a chart to view the lowest and highest-priced products.
3. Select a number on the X-axis to view a dialog box showing the number of products priced at that exact amount.
4. Click on the Equals filter to display the product details.
Creating a Relational Data Model
1. Click Apply on the top left of the ribbon to load the changes to the data model.
2. Select the table view on the left pane to display the data tables. Link them using the following primary keys:
- Date from calendar to sales.
- product_id field from products to sales.
- store_id from stores to sales.
Make a data model in seconds to generate your KPI report.
3. Create a date hierarchy. Click the three dots (…) to the right of Start of Month in the Data pane to apply Start of Week and Date under it.
4. Click the eyeball icon on the right of the column to hide the ones you don’t need to show.
Adding Calculated Columns and Measures
What’s the difference between calculated columns and measures?
A measure is calculated for a cell in a DAX query or report and has a single value, like total revenue.
A calculated column has a value in every row, like the revenue per product, which is the number of units sold times the price (see the image below).
Go beyond raw data with calculated columns in a KPI report.
Import product cost and product price from the products table to calculate revenue and profit. Here’s how.
1. Click Table Tools > New Column. Write a formula for cost using the DAX function RELATED.
Cost = RELATED (products [product_cost])
It tells Power BI to pull product_cost from the products table into the sales table.
2. Again, click Table Tools > New Column. Write the DAX formula to import the product_price into the new column.
Price = RELATED (products [product_price])
3. Add new columns as above and calculate the revenue and profit.
Revenue = sales [Units] * sales [Price]
Profit = sales [revenue] – (sales [cost] * sales [units])
4. Select the report view from the left pane and click the sales table in the Data pane on the right.
5. To create a new measure, navigate to the Calculations tab on the ribbon and click New Measure. Alternatively, you can click the three dots (…) next to the sales table in the Data pane on the right.
6. Calculate the total orders using DISTINCTCOUNT to count the number of rows. (Each row represents one transaction.)
Total orders = DISTINCTCOUNT(Sales[sale_id])
7. Write the DAX for total revenue and total profit using the calculated columns for revenue and profit.
Total revenue = Sum (Sales[revenue])
Total profit = Sum(Sales[profit])
Building an Interactive Report
Select your visualization type before you drag the data onto the canvas. The KPI and card visuals seem identical, but they aren’t. The KPI visual object requires defining a trend axis, like in a graph, while the card visual displays a number on a card.
Visuals embellish a KPI report, making it easily readable.
1. Select the KPI visual and select a trends axis, in this case, the date hierarchy.
2. Drag and drop the calculated measure Total Revenue onto the canvas and watch as the KPI visual populates with the revenue. Do the same for Total Profit.
3. Add a slicer for store location to view the total orders, revenue and profit by area.
Define the X and Y axes for bar and line charts if needed.
Displaying smaller objects at the top and the more elaborate objects like charts at the bottom is a dashboarding best practice. It gives the reader top-down information about their KPIs.
The paintbrush tool in the Visualization pane at the right offers many customization options. You can position and rename objects, change the background and modify page settings.
Drill down into the total orders and revenue figures in a retail KPI report.
Many readers ask about the difference between a KPI dashboard and a KPI report. While a KPI dashboard provides a high-level performance view, a report is more analytical.
What did the above KPIs tell me?
- With the above data, I could track total orders from January 2022 to September 2023 and calculate the revenue and profit.
- The calendar data could help me identify spikes and dips in demand while eliminating seasonality like holidays and back-to-school-season.
- Above-average revenue but low profits could indicate a higher product cost. Maybe my company should change suppliers? KPIs are extremely valuable for decision-making.
Additionally, KPIs are excellent tools for goal setting and focusing on activities that actually move the needle. Tracking them imparts accountability and motivates teams to strive to do better. Seeing progress reflected in data is a powerful driver.
Pitfalls and Best Practices
While KPIs are excellent performance-tracking tools, being mindful of their limitations can save you heartburn later.
- Having too many metrics to track can block deep analysis. Focus on prioritizing a handful of KPIs (ideally 5-10) that truly matter.
- Avoid tunnel vision. Overly narrow KPIs can restrict your view, but the correct metrics can add to the insight. While buyer reviews are important, adding social media sentiment analysis can give you a better picture of customer satisfaction.
- Don’t ignore outliers; analyze them. A sudden spike in user engagement is great news, but what does it mean? Is it a one-time event or a sign of a new trend?
This way, KPIs become a launchpad for success, not a roadblock.
As a data analyst, which metrics should you pick as KPIs? It depends on your domain, business and market dynamics.
We reached out to our people in the trenches for advice.
Taylor Knowles, our Senior Director of Customer Success, told me his thought process when determining which KPIs to use.
I always try to identify a KPI that gives me a high-level view of performance first. After that, if I notice that KPI is changing I can drill down and look at other more specific criteria to determine what has changed and why.”
Farid Asadi, our Conversion and Experimentation Manager, had this to share.
My aim is to concentrate on the metrics that truly matter, presenting them in a clear and accessible way through thoughtful data visualization. This approach stems from the idea that trying to display every possible metric can actually hinder our ability to uncover deeper, more meaningful insights.”
Evaluate your KPIs against the SMART criteria. Are they specific, measurable, attainable, relevant and time-bound?
- Specific: Guard against vague metrics. What exactly are you measuring? If customer satisfaction is a significant KPI, should it be a combination of CSAT (Customer Satisfaction Score) and NPS (Net Promoter Score)?
- Measurable: Check if you can quantify your KPIs and set targets. Reducing average call handling time by 10% within the next quarter would be a good KPI.
- Attainable: They should be challenging enough for you to strive to achieve the outcomes but realistic and achievable.
- Relevant: Your KPIs should map to your business objectives. Focusing on website traffic as the sole KPI for your eCommerce business can be insufficient when you’re trying to boost traffic. Would the conversion rate be a good one?
- Time-Bound: Setting a time frame imparts structure to goal setting.
Knowles shared this helpful tip for data analysts new to the KPI-setting process.
Beware of focusing on a very specific single metric without taking the bigger picture into mind. It is easy to come up with a hundred KPIs to track but the trick is knowing which ones are going to truly impact your business and focus on those.”
Involve stakeholders and double-check KPIs before sharing them with your team.
And last but not least, review them regularly.
KPI Reporting Software
Choosing a KPI reporting platform can be overwhelming! Here’s what to look for:
- Easy Source Connectivity: Using data from your preferred sources should be a matter of a few clicks and shouldn’t require asking the IT team.
- Visually Appealing Reports: Think of charts and graphs that are clear and easy to understand. Pre-made templates are great starters, and the option to create unique visualizations keeps your message on point.
- User-Friendly Model Building: Building models to understand your data shouldn’t feel like solving a complex puzzle. My experience with Power BI is a case in point.
- Self-Service BI: Independent data exploration and report building can drive a data culture and make everyone a data analyst. A drag-and-drop interface is handy when managing multiple KPIs and producing reports quickly.
- Predictive Analytics: This feature is usually paid and can keep you two steps ahead of the prevailing trends.
FAQs
Identify the KPIs to benchmark by reading industry reports, publications and public financial reports.
Benchmarking is effective only as an apples-to-apples comparison. Compare your company with other organizations in the same industry. They should have a similar number of employees, market penetration, business model and target audience.
Once you’ve finalized the KPIs to compare, use visualization tools to create comparison charts for deep analysis.
Use benchmarking not only to compare but also as an improvement exercise. Identify your strengths and weaknesses and brainstorm on where and what to improve. Use data to inform your strategy.
An easy way to do this is via subscription-based benchmarking services. You can find them online. Remember, benchmarking is a strategic tool, not a race.
It depends on your industry and the stage of your business.
- Quarterly or bi-monthly check-ins would be ideal for companies in fast-paced industries where trends, technology and customer behavior change frequently.
- Semi-annual or annual check-ins should suffice for industries that don’t see much change or disruptions.
- Startups and early-stage businesses should circle back to their key metrics every two to three months, while annual reviews with quarterly check-ins should be enough for established organizations.
In special cases, you can have a KPI review when the situation demands it. Internal product updates, leadership changes or external disruption like an economic downturn will require a KPI revisit.
You’ve been sharing KPI reports for a while, but are they helpful? Asking your end users will help you fine-tune report building.
- Reach out to people with surveys and conduct individual interviews. Create focus groups with department managers, executives and analysts.
Aim for a mixed group in terms of experience and skills. Ask them if the information was easy to locate. Were the KPI reports easy to navigate? Did they assist in decision-making?
- Use data and analytics to track report usage. Test alternative report layouts and design using A/B testing.
- Generate heatmaps and view session recordings of how people used the reports. Learn how people navigate the reports and which features they use.
- You can also conduct Q&A sessions and invite suggestions.
Next Steps
Hopefully, my experience gave you the confidence to create a KPI report. Whichever tool you use, start properly by determining your KPIs and data sources and selecting an efficient KPI tracking tool. All the best!
Get our free requirements template to select a solution with features that matter to you. Evaluate products in an apples-to-apples comparison with analyst scores based on deep research and a 400+ point analysis of each product.
Do you have any questions about the above how-to steps? Ask away in the comments below!
SME Contributors
Nate LaFerle has been a trusted advisor to some of the world’s largest organizations tackling complex data migration & governance challenges, leading high-impact global project teams at clients including 3M, American Airlines, and Johnson & Johnson.
As a consulting talent leader, he oversaw the career & learning programs for over 600 global data consultants and partners at Syniti, a leading data management software and services provider. He continues to advise clients and deliver solutions through his independent consulting practice, Remisphere Digital (www.remisphere.com).
Farid Asadi is the Conversion & Experimentation Manager at SelectHub. He designs growth and conversion optimization programs that turn hypotheses into cash flow. He writes about conversion optimization, analytics, metrics and experiments.
Taylor Knowles is the Senior Director of Customer Success at SelectHub. He has 13+ years of SaaS experience at multiple successful startups and a deep understanding of the SaaS and technology landscape. He is passionate about using data to help his internal teams and partners succeed and grow. He enjoys traveling, cooking, and playing golf.