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 @dataveld. Also 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.Â
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.
It was one of last week’s challenge requirements (remove null values and Conference Median), my numbers match.
My Numbers were basically the same but at the Billion level and not the Million
You can customize the detail level in the visual settings and switch from billions to millions.
Great Challenge. Thank you very much for your effort 🙂
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.
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!
Hello, I need some help please while creating the new column “Total Profit” i have an issue concerning the agregation, I only get “Number”, “first one” and the “last one” which means i don’t get the sum nor the medium and obviously I need the sum.
Hi Zineb! Double check that the column of interest is formatted as a number.
While the Https gets renewed, this is the link to the video
https://www.youtube.com/watch?v=UTzOGBgPGMw
Trying to learn power bi, just did this one and am looking forward to going through the series. The videos are great to explain how the reports are generated. I can’t publish as I am not using a work or school account, but mine looks like it should!
We’re so glad you’re participating! In the future, you can use the M365 developer program to get a free Power BI license for learning! Check out how to sign up in our blog post.
I have different values., Kindly help me out…
Revenue 81461M, Expenses 78140M, Profit 3469M
Hi Selva,
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.
Where is the Total Profits in the Finances-Fact?
You have to create it in Power Query. This is listed in the first requirement.
Hi J – total profits appears to be revenue minus expenses. You can create this using a measure. I hope this helps!
HI , My number were completely different , Revenue were 89,101 and expense were 85481 and but the profit is 151 can anyone explain why it is because I used power query and the formulla was total revenue – total expense + extra expense…..Can anyone explain..
In week 1, the instructions included filters on NCAA subdivisions and FBS Conferences. If you add those filters, your numbers will match.
Pingback: Week 02 #WorkoutWednesday Power BI challenge – KiwiMaori
My numbers for the KPI’s are different. Am I doing something wrong here? Spencer…or anyone else.
Total Revenues — 287.36 Million
Total Expenses — 217.36 Million
Total Profit — 191.76
Appreciate your response. Thanks
The data hasn’t changed, so I think your numbers should match. Make sure you don’t have any filters or cross-filters applied.
Enjoying learning power BI here.
I ran through the scenario twice to make sure I understood and caught the nuance of showing labels and removing the x-axis after the second time.
Thank you Rob
Solution to this challenge https://youtu.be/D-5DttcMLK8?si=FA2W40jIEy5Vt3UF