This post covers Data Aggregation.

• Aggregation
  • Gathering and Summarizing
    • sum of sales
    • compute average
    • count distinct values
    • product with maximum sales/profit

Data Aggregation

• Import data set and create chart for sales of different countries
  • Country to Rows
  • Sales to Columns
• Select sales icon in row/columns and change the function from sum to Average
• Which country has the highest average sales (assuming no filter applied)
  • Lesotho
• How much is the average sales for the county that has highest sales
  • 1,119
• Create a chart of Total Profit vs Product ID
  • Which product has the highest profit: TEC-CO-3691
  • Which product has the highest loss: TEC-MA-3854
• Drag Product ID to Filter; select Top by Field; Top 50 Profit by Sum
  • Which product has the least profit among top 50 profitable products - OFF-ST-4266
• Drag Order Date to Columns and Sales to Rows; Select Quarter Year from Order Option and Sum from Sales
  • Which Quarter Year has the highest sales - Q4 2015

Scatter Plot

• Drag Profit/Sales to Rows/Column
• Change Sum to Average
• Drag Country to Color
• Filter on total Sales
  • At least 100,000
• Drag Country to Label
• Right Click axis and select Edit Axis

Task

• Right click Sales and Create -> Calculated Field
• IF [Sales] <= 10000 THEN “<=10,000” ELSE “>10,000”
  • Name this as Sales (Grouped)
• Drag Sales (Grouped) to both Rows and Column
  • Convert Columns to Measure -> Count Dictinct
• Drag Category to color Card
• Which Category has been in both sales groups
  • Technology

Task

• Drag Profit to Columns
• Change to Standard Deviation
• Drag Country to Rows
• Filter Year on 2014 and 2015
  • If no of years are more then years can be converted to continuous measure and select range of values in filter
• Sort based on Std Dev of Profit
• Which Country has highest std dev in profit
  • Lithuania