BYU Student Author: @Sterling_Lane
Professional Author: @Todd_A_Allen_CPA
Reviewers: @Millie_K_B, @Jimmy_Han
Estimated Time to Solve: 45 Minutes
This challenge was written in partnership with an accounting professional. The data, scenario, or both were inspired by real-world business scenarios.
We provide the solution to this challenge using:
- Power BI
Need a program? Click here.
Overview
You are an accounting intern working at BYU Broadcasting. Studio C is a very popular show for the platform and has been for a while now! Each month, your marketing team sends viewership and revenue data directly exported from YouTube to the Controller. The Controller used Excel for analysis in the past, but struggles with Excel’s long processing and loading times as well as issues with repeatability (since this analysis needs to be done every month). You explain that PowerBI may be a more efficient analytical tool given these concerns, and your Controller is impressed!
Now your Controller has given you the opportunity to generate an example of the Studio C monthly revenue report yourself for December 2023 using a partial dataset. Your goal is to create a report that can be used every month and shows three things: (1) Revenue over time, (2) Season-by-Season Revenue Breakdown, and (3) Top 10 sketch titles by total views.
Instructions
- Download the Sketch List.xlsx and YouTube_General.csv files and load each of these into Power BI.
a. For the Sketch List, Transform it prior to loading by ensuring the “Season” column loads as Text type instead of Int64.Type. Check the hints if you get stuck here. Keep only the first three columns.
b. YouTube_General.csv loads with lots of unnecessary columns. Keep only these five columns: Day, Asset Title, Custom ID, Owned Views, and Partner Revenue.
c. Define the relationship between the tables. For these tables, the UniqueID in the Sketch List table matches the CustomID in the YouTube_General table. One UniqueID in the Sketch table can be found multiple times in the YouTube_General table (one to many relationship). - Clean the Youtube_General data further using Power Query
a. Ensure the “Day” column is formatted as a date/time type instead of a number type.
b. Add a column called “Week” which assigns each value to the week of the month it belongs to based on the day of the date in the “Day” column. Assume Days 1-7 are the first week, 8-14 are the second week, 15-21 are the third week, and 22-31 are the final week. These should be numeric values (1 - 4) rather than ordinal values. - Create a page called “Total Revenue Breakdown” that highlights total December revenue
a. Add a bar chart showing total revenue per day in December. Adjust the labels on the X and Y axis to state “Day of the Week” (as an ordinal value) and “Daily Revenue Total” respectively. Title the chart “Total December Revenue by Day” and change the colors of the bars to an orange color with a light blue background.
b. Add a slicer so that we can filter the total revenue across each week in the Vertical List style. Ensure the background and text colors match the same colors you chose for the chart. Ensure you choose the classic Power BI slicer and not the “slicer (new)” slicer for this.
c. Add two cards (make sure to use the classic card for these and not the “card (new)” option) displaying the average daily revenue (titled “Average Daily Revenue”) and max daily revenue (titled “Daily Revenue Peak”). Both values should be calculated using Quick Measures in Power BI. Ensure that the average daily revenue does NOT change but the max daily revenue DOES change when we toggle different weeks on the slicer. Ensure the background and text color on the cards matches the color of the chart and bold the values to emphasize them. - Create a separate page titled “Season by Season Breakdown” which will break down total revenue and viewership by season.
a. The values in the “Season” column of the Sketch List include compilations (“1-7” means it’s a compilation of sketches from Seasons 1-7). We want to analyze these compilations in their own category. Use PowerQuery to add a new column called “Cleaned Season” to the “Sketch List” table to address this. Columns that contain a “-” in the middle (i.e., 1-7) represent compilations and should go into their own category called “Compilations”. All other values should be the same as the “Season” column.
b. Create a Treemap displaying total revenue by each cleaned season for December.
c. Filter out the blank values in the Treemap. These represent additional BYU Broadcasting YouTube content that is out of scope.
d. Format the Treemap so that the “Cleaned Season” values are bolded. The title of this Treemap should be “Total Studio C Revenue by Season” and should be bolded and centered.
e. Create a table that shows the top 10 Studio C sketches and the number of views they got. This should interact with the Treemap so that clicking a season on the Treemap will change which sketch titles display on the table. Disable the totals on the table and make sure to filter out any null YouTube sketch titles.
f. Add a Slicer where we can select the day of the week in a vertical list style. - Use the “Season by Season Breakdown” page to answer a few questions that interest you:
- What was the most popular Studio C sketch on Christmas Day?
- What was the most popular Studio C Compilation sketch on Christmas Day?
- How many Christmas-themed sketches were in the Top 10 total views in December?
Data Files
Suggestions and Hints
-
When loading the “Sketch List”, Power BI will attempt to convert the data to a number based on the first few rows. To change this directly without loading the data first, you can go to the “Applied Step” (titled ‘Changed Type’ by default) and directly change the “Season” column to a text data type in the code.
-
Power BI cannot convert number data types directly to dates. Consider first converting the numeric data to a different data type before attempting to converting it to a date type.
-
When adding a column in Power Query, conditional logic requires if/else if/then syntax to be lowercase. Additionally, make sure you are assigning the weeks based on the Day of the Date column. This is done by calling “Date.Day(column)” within your condition.
-
The format for conditional statements when creating conditional columns is below:
if condition then result else if condition then result else result -
All required measures can be created using the “Quick Measure” feature in Power BI.
-
To modify a slicer’s interactions, highlight the slicer and toggle the ‘Edit Interactions’ button within the format menu.
-
Text.Contains may be helpful for identifying compilations in the “Season” column in Power Query.
-
Filters can be customized to only affect specific visuals or an entire page
-
You can use a “Top N” filter on the “Total Views” table to get the top 10 results. You can choose this as a filter type on any categorical variable (in this case, “SKETCH YOUTUBE TITLE”). The “By Value” would be by "Sum of Owned Views” since that’s what the table is displaying.
Solution
