2021 Week 2 | Power BI: Creating Basic KPI Report

Introduction

Thanks to everyone who participated in Week 1 of #WorkoutWednesday – Power BI edition. If you are joining this week, you may want to revisit the first week’s challenge as this week will build on the data model that we created in W01. We also have the PBIX file available here. This week we are going to be creating a basic KPI report. We will be incorporating important tenants of KPI reporting such as BANs, trends, and breakdowns. 

Requirements

  • Create a new field in Power Query (using M) called Total Profits. This field will be calculated by subtracting Total Expenses from Total Revenues and then adding back in Excess Transfers Back.
    • The background on this is that in the past few years, the NCAA has allowed schools to exclude money that was transferred back to the main institution (from the athletic department) from showing as profits. By adding back in Excess Transfers Back we are finding the total amount of money generated by the different athletic deparments
    • Definition: Positive net revenues generated by athletics and transferred to the institution for non-athletics purposes. These funds are in excess of the transfers subtracted from the institutional and governmental funds allocated to athletics. (from http://cafidatabase.knightcommission.org/about-the-data)
  • Create KPI dashboard that includes 3 high level measures: Total Revenues, Total Expenses, and Total Profits. 
  • Must include a trend line for each category and breakdown by conference.
  • Indicate that this view is the Summary level view within the header area. 
  • Colors and design of the header section is at your discretion.

Dataset

This week uses a data set that breaks down NCAA athletic department expenses and revenues by year. You can get it here at data.world. This is the same data set that was used in W01. 

Share

After you finish your workout, share on Twitter using the hashtags #WOW2021 and #PowerBI, and tag @JSBaucke@MMarie, @shan_gsd and @dataveldAlso make sure to fill out the Submission Tracker so that we can count you as a participant this week in order to track our participation throughout the year. 

Solution

6 thoughts on “2021 Week 2 | Power BI: Creating Basic KPI Report”

  1. Keep in mind that your numbers will differ slightly from what is shown because it appears that conference medium was removed from the NCAA Division after the video / files were created.

  2. Hey, out of interest what are the advantages of adding the Total column in the power query rather than adding a new column to the table? Hopefully that make sense, I have been doing quite a bit of data visualisation but very new to the manipulation side of things.

    1. Although this use case may not be the most efficient (as you could have created a calculated measure instead), there are still plenty of use cases where doing your row level logic in the data model layer as opposed to a calculated column can increase your performance. Hope that helps!

Leave a Reply to Everett E Jackson Cancel Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top