Assignment Detail:- Scenario
Upon reviewing the growth report you created, Lucy asked for a report that shows composition of Product Categories and Sub Categories based on certain filters, including Year, Country, Customer Gender, and Age Group-
Specifically, Lucy wants to see the report visualized using a hierarchical chart-
What You'll Need
• A computer with Microsoft Excel installed on it- We prefer Excel 2016, but you can still complete the lab using Excel 2013 or Excel 2010-• File Lab7Start v5-xlsx-
Lab 7
Download the lab file Lab7Start v5-xlsx to answer the questions below-Let's add another sheet to the workbook and name it Composition-Start by adding a new pivot table with Year, Country, Customer Gender, and Age Group as Filters, Product Category and Sub Category as Rows, and Sum of Revenue as Values-Format the pivot table to show in Tabular form, do not show subtotals and grand totals, and repeat all item labels-
Now, copy the structure of the rows and columns to the cells next to the pivot table, that is the Product categories and Sub categories as Rows- Delete the data area for now-Populate the data area of the newly created cross tabular structure using SUMIFS-- function-Assuming that you have:???? F7: Product Category eg- "Accessories"???? G7: Sub Category eg- "Bike Racks"???? C1: Year filter???? C2: Country Filter???? C3: Customer Gender Filter???? C4: Age Group FilterThen, the cell that corresponds the Product Category Accessories and Sub Category Bike Racks would have the following formula:=SUMIFS-SalesTable-Revenue-,SalesTable-Product Category-,$F7,SalesTable-Sub Category-,$G7,SalesTable-Year-,IF-$C$1="-All-",">0",$C$1-,SalesTable-Country-,IF-$C$2="-All-","*",$C$2-,SalesTable-Customer Gender-,IF-$C$3="-All-","*",$C$3-,SalesTable-Age Group-,IF-$C$4="-All-","*",$C$4--Once your table is populated, remove the fields from the Rows, Columns, and Values of the Pivot table, so that only the filters remains- Align the filters with your cross tabular report- Create a treemap or sunburst chart based on the data in the cross tabular report- Name the chart "Sales Composition"-
With your pivot table, sort the Sub Category column, by Descending order of Sum of Revenue- This way, whenever you change the filter and the sub category composition changes, they will still be presented in descending order within the product category-Now, let's show percentages of a sub category within the product category-Add an extra column next to the Sum of Revenue column, by adding another Sum of Revenue as Values- Go to Value Field Settings and give the column title "% of Product Category"- Go to Show Value As tab and select % of Parent Row Total-
Attachment:- Data flow diagram-rar
Most Recent Questions