How to Build a KPI Report with Power BI

The Power BI KPI demo report is a demonstration of how a typical KPI report will look. The report uses data from the fictitious Microsoft Contoso Retail Datawarehouse which is stored in a SQL Server Database. I’ve simply renamed it to rn retail for demo purposes.

View the Demo Report on Youtube:

The report is currently divided into 5 pages.

1. The first page is a typical style KPI report, leading with the financial KPI, then breaking down to the store type, and then some online KPI. As the database is quite old (going back to 2009), it doesn’t have a lot of person-level information, but does for online (hence online KPIs are included here). The KPIs are presented here in comparison to the previous year’s performance, but targets would also typically be included.
   The month selected allows the user to go back to any point in time in the year (or years if more data was included).
   
   
   
   2. The second page covers sales MTD, QTD, and YTD vs. the previous year there are graphs showing the YoY breakdown by store type and a table allowing more drill down into the individual store performance YoY. This interactive tool allows users to quickly pinpoint some of the reasons responsible for higher or lower YoY performance. The measure calculations can be found lower down this page.
   
   
   
   3. The third page of the report goes into more detail on stores broken down by country and city and includes the sales per square feet calculation. Again this helps the user explore the data to identify best and worst-performing stores in more detail.
   
   
   
2. The fourth page, shows sales by product category and product, allowing you to drill down from the top category levels. There are then graphs for Monthly and yearly performance.
   
   
   
   5. The fifth page focuses on activities. This can be developed further to show actives by activity segment (e.g. new, active, reactive, lapsed, etc. once activity segmentation is added to the model)
   
   
   The Data Model:
   
   
   
   
   Time Intelligence DAX measures
   The DAX measures for MTD, QTD and YTD and comparisons to the previous year are below:

//Month to Date Sales
Sales MTD = CALCULATE(SUM(FactSales[SalesAmount]) , DATESMTD('Date'[Date]))

//Quarter to Date Sales
Sales QTD = CALCULATE(SUM(FactSales[SalesAmount]) , DATESQTD('Date'[Date]))

//Year to Date Sales
Sales YTD = CALCULATE(SUM(FactSales[SalesAmount]) , DATESYTD('Date'[Date]))

//Last Year Month to Date Sales 
Sales LY MTD = CALCULATE(SUM(FactSales[SalesAmount]) , SAMEPERIODLASTYEAR(DATESMTD('Date'[Date])))

//Last Year Quarter to Date Sales
Sales LY QTD = CALCULATE(SUM(FactSales[SalesAmount]) , SAMEPERIODLASTYEAR(DATESQTD('Date'[Date])))

//Last Year Year to Date Sales 
Sales LY YTD = CALCULATE(SUM(FactSales[SalesAmount]) , SAMEPERIODLASTYEAR(DATESYTD('Date'[Date])))

//Variance Month to Date vs. Last Year Sales 
Sales VAR MTD vs. Last Year = [Sales MTD]-[Sales LY MTD]

//Variance Quarter to Date vs. Last Year Sales 
Sales VAR QTD vs. Last Year = [Sales QTD]-[Sales LY QTD]

//Variance Year to Date vs. Last Year Sales 
Sales VAR YTD vs. Last Year = [Sales YTD]-[Sales LY YTD]

//Variance % Month to Date vs. Last Year Sales
Sales VAR% MTD vs. Last Year = ([Sales MTD]-[Sales LY MTD])/[Sales LY MTD]

//Variance % Quarter to Date vs. Last Year Sales
Sales VAR% QTD vs. Last Year = ([Sales QTD]-[Sales LY QTD])/[Sales LY QTD]

//Variance % Year to Date vs. Last Year Sales
Sales VAR% YTD vs. Last Year = ([Sales YTD]-[Sales LY YTD])/[Sales LY YTD]