Lab Practice

Part 1: Creating KPI Text Boxes

• KPI - Key Performance Indicators from Investopedia
• Sales KPI Examples from Klipfolio
• We will create three dynamic Text Objects showing Sales, Margin%, and Number of Orders
• To avoid errors, copy the expressions below for each text object:
  • Sales text object: ='Sales: ' & Num(Sum(LineSalesAmount), '$#,##0')
  • Margin% text object: ='Margin%: ' & Num(Sum(Margin)/Sum(LineSalesAmount),'##.##%')
  • Number of Orders text object: ='Orders ' & Num(Count(Distinct OrderID), '#,##0')

Dimensions and Measures

• Dimension: It is a descriptive field in the data set which represents few distinct values. Examples - Month, Year, Product ID etc.
• Measures: It is a numeric field on which some calculations are performed for each distinct value of dimension.

Part II:

• Compare Discount and Number of Orders per Customer (Company Name)
  • Chart: Scatter Chart
  • Dimension: Company Name
  • Measures: X axis - Discount, Y axis - Order ID
• Display Sum and Avg of FreightWeight per Customer (Company Name)
  • Option 1: Create TWO bar charts, Dimension - Company Name, Measure (Expression): Sum(FreightWeight) in first chart and avg(FreightWeight) in second chart
  • Option 2: Create ONE bar chart with Dimension - Company Name, and TWO Measures (Expressions): Sum(FreightWeight) and avg(FreightWeight). On Axes Tab, select avg(FreightWeight) and Position Right(Top)
  • Option 3: Create ONE bar chart with Dimension - Company Name, and TWO Measures (Expressions): Sum(FreightWeight) and avg(FreightWeight) and GROUP both expressions into Expression group: on Expression Tab, select second Expression and then select Group
• Create Dimension Group - Year, Month, Quarter and display Sum of FreightWeight (See Part I for instructions on how to create Dimension Group)
  • Bar Chart
  • Dimension Group that includes Year, Month, Quarter
  • Expression: Sum(FreightWeight)
• To the chart above add additional expression avg(FreightWeight) and create Expression Group to display Sum and Avg of FreightWeight
• Add ShipperID and SupplierID list boxes. Analyze FreightWeight per Supplier/Shipper. Identify companies that works with specific Supplier/Shipper. Use bar chart you created earlier.
• Add CategoryName/Country/City list boxes. Create chart/charts to analyze sales in different categories per Country/City.
  • Possible dimensions: CategoryName/Country/City
  • Expression: sum(LineSalesAmount)
  • Option 1: bar chart with CategoryName as Dimension and Expression: sum(LineSalesAmount) and make selection in County/City list boxes to perform analysis
  • Option 2: bar chart with two dimensions: Country and CategoryName (pay attention on order of the dimensions) and Expression: sum(LineSalesAmount), style: stacked, sorted, and make selections in City list box. Try to change the order of the dimensions and observe the result.
  • Option 3: Pie or Funnel or Block Chart: Dimension: CategoryName and Expression: sum(LineSalesAmount) and make selection in County/City list boxes to perform analysi