Reference no: EM133125074

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 Filter
Then, 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