If are you seeing it for 1st time do check out the Ist tutorial here.
While your first attempt to show the company’s performance to Lucky was not bad, clearly he has a lot more requirements than what you provided. he wants to know more about the year over year sales, sliced into different categories, sub-categories, and countries. he also wants to see additional information such as customer demographics. He is not smart so he needs every detail in hand.
Data is provided by jacky lab2data.xlsx
Things to do :
The first thing you need to do is to convert the data into an Excel table.
Add a total row for the table, and use the Sum aggregation to show the total of the Revenue column and then filter the data only for United States.
Use : Autosum
First, let’s add a “Month” column. Insert a new column to the left of the Customer ID column, and use formula to derive the month of sales from the Date column.
Next, let’s add an “Age Group” column. Remember to clear any filters you previously applied. Insert a new column to the left of the Customer Gender, and use formula to derive the age group from the Customer Age column. Let’s group the customers based on the following criteria:
- Youth (<25)
- Young Adults (25-34)
- Adults (35-64)
- Seniors (>64)
Use this to bar =IFS([@[Customer Age]]<25,”Youth”, AND([@[Customer Age]]>=25,[@[Customer Age]]<=34),”Young Adults”, AND([@[Customer Age]]>=35,[@[Customer Age]]<=64),”Adults”,[@[Customer Age]]>65,”Seniors”)
Now, let’s add a “Frame Size” column. Insert a new column to the left of the Order Quantity, and use a combination of the IF() and RIGHT() functions to derive the frame size of a bicycle from the last two characters of the Product column, when the Product Category is Bikes. Otherwise, leave it blank.
Find profit of the table
Do it yourself
Congratulations on finishing this step.