Exercises

Exercise 1 – Create a new analysis

  1. From left panel, choose Datasets.
  2. Click on SaaS-Sales dataset.
  3. Click Create analysis button.
  4. Add - Advanced Calculations suffix to the analysis name.
AuthorWorkshop-Module5-Exercise

Exercise 2 – Add Cost calculations Cost is Sales minus Profit. It is an additive measure. We will create two calculated fields for Cost (Row and Aggregate) and compare results.

  1. Click + Add and select Add calculated field.
  2. Paste in following details and click Save.
    Calculation name: Row Cost
    Calculation: {Sales} - {Profit}
  3. Repeat above steps to create one more calculation as given below.
    Calculation name: Aggregate Cost
    Calculation: sum({Sales}) - sum({Profit})
AuthorWorkshop-Module5-Exercise

Exercise 3 – Create a table to compare the calculations

  1. Change name of new sheet to Row vs Aggregate Calcs.
  2. With the blank visual in focus, choose Table from Left panel visual types section.
  3. From Left panel field list, click on Segment, Profit, Sales, Row Cost and Aggregate Cost fields.
  4. Open Visual menu and click Show totals.
AuthorWorkshop-Module5-Exercise

Note - Since Cost is additive, both Row and Aggregate level calculations give the same result. In such cases, use the Row level version and include it in the dataset layer to have the row level calculation done ahead of time along with SPICE dataset refresh.

Exercise 4 – Add Profit Ratio calculations
Profit Ratio is the Profit divided by Sales. It is a non-additive measure. We will create two calculated fields for Profit Ratio (Row and Aggregate) and compare results. This will help you get a clear understanding of how such calculations work.

  1. Click + Add and select Add calculated field.
  2. Paste in following details and click Save.
    Calculation name: Row Profit Margin (Incorrect)
    Calculation: {Profit}/{Sales}
  3. Open field menu for newly added calculation, click Show as and choose Percent.
  4. Repeat above steps to create one more calculation as given below.
    Calculation name: Aggregate Profit Margin
    Calculation: sum({Profit})/sum({Sales})
AuthorWorkshop-Module5-Exercise

Exercise 5 – Add Profit Ratio calculations to table

  1. Click the table visual to ensure it is in focus (blue highlight around it).
  2. From Left panel field list, click on Row Profit Ratio (Incorrect) and Aggregate Profit Ratio fields.
AuthorWorkshop-Module5-Exercise

Note - Row Profit Ratio calculation at data row level. Thereafter, it is getting summed up just like a regular aggregate measure and hence ends up being incorrect at the Segment and overall total levels. You can see that the aggregation mode against this calculation in the field well is SUM. The correct version gets the sum of profit and sum of sales for each Segment and then does the division. For the total row, it uses the total profit and total sales. Hence, we get the expected results. See that the aggregation mode is CUSTOM for this calculation. So, build calculations for non additive KPIs using aggregated measures.

Exercise 6 – Add Level Aware Aggregate Calculations.

  1. Click + Add and select Add calculated field.
  2. Paste in following details and click Save.
    Calculation name: Overall Sales [PRE_FILTER]
    Calculation: sumOver(Sales,[],PRE_FILTER)
  3. Repeat above steps to create two more calculated fields.
    Calculation name: Overall Sales [PRE_AGG]
    Calculation: sumOver(Sales,[],PRE_AGG)
    Calculation name: Overall Sales [POST_AGG_FILTER]
    Calculation: sumOver(sum(Sales),[],POST_AGG_FILTER)
AuthorWorkshop-Module5-Exercise

Exercise 7 – Create a table to compare LAA calcs

  1. Click + icon in sheet tab section to create a new sheet.
  2. Rename the sheet as Level Aware Aggregates.
  3. With the blank visual in focus, choose Table from Left panel visual types section.
  4. From field list, click on Industry, Sales, Overall Sales [PRE_FILTER], Overall Sales [PRE_AGG], Overall Sales [POST_AGG_FILTER] fields.
  5. Open Visual menu and click Show totals.
AuthorWorkshop-Module5-Exercise

Note - At this point, all three calculated fields should be showing Overall Sales total across the full data set. ie - same value as total row of Sales column in this view Then, why did we create three variants of this calculation? Following exercises will bring out the differences in these calculations.

Exercise 8 – Add a filter

  1. In the table, click Sales column header and click descending sort icon.
  2. From left panel field list, open field menu for Industry and click Add filter for this field.
  3. From Filter panel, open filter menu for Industry and click Add to sheet.
  4. Open the dropdown from the on sheet control and deselect Misc.
AuthorWorkshop-Module5-Exercise

Note - You will see that PRE_FILTER value stayed the same (as they get evaluated ahead of analysis filters) whereas PRE_AGG, POST_AGG_FILTER and table total for Sales column dropped down to new value excluding Misc category.

Exercise 9 – Add a Top N filter

  1. With table visual in focus, click + against Filters in left panel and choose Industry.
  2. Click the newly added Industry filter and change Filter type to Top and bottom filter.
  3. In Show Top box, enter 5.
  4. In By box, select Sales.
  5. Click Apply button.
AuthorWorkshop-Module5-Exercise

Note - You will see that PRE_FILTER value and PRE_AGG values stayed unchanged from their prior values (as they get evaluated ahead of Top/Bottom filters) whereas POST_AGG_FILTER and table total for Sales column dropped down to new value including only Sales for Top 5 Industries.

Exercise 10 – Create a pivot table

  1. Click + icon in sheet tab section to create a new sheet.
  2. Rename the sheet as Table Calculations.
  3. With the blank visual in focus, choose Pivot table from Left panel visual types section.
  4. From left panel field list, click on Region and Subregion fields.
  5. Drag and drop Segment field into Columns field well.
  6. Drag and drop Sales into Values field well twice.
AuthorWorkshop-Module5-Exercise

Exercise 11 – Add table calculation

  1. Resize the Pivot table and then open it’s field well.
  2. From second Sales instance’s field menu, click/hover on Add table calculation and select Percent of total. (Default mode of table across gives the percent contribution of each Segment towards overall sales in each Subregion.)
  3. From second Sales instance’s field menu, click/hover on Calculate as and select Group down. (This gives percent contribution of each Subregion towards overall sales in each Region per Segment)
  4. From Visual menu, click pencil icon to open Format visual panel.
  5. Open Value names section and enter % of Total into custom name field for second instance Sales field.
  6. Try other Calculate as modes as well.
AuthorWorkshop-Module5-Exercise