Replicate Subtotal Field as Calculated Field

[Aurelien]

Hello,

I built a QuickSight pivot table with several levels of details, e.g. Region, Country, Customer and Store. I have Sales Revenue for a specific week, and Sales Revenue for the previous week (not displayed).

I would like to create a calculated field (in green) that replicates the results of the standard Subtotal field.
Examples:

• If only the Region field is displayed, calculated field should display the sum partitioned by Region,
• If only the Region and Country fields are displayed, calculated field should display the sum partitioned by Region and Country,
• …

Basically, I would need a sumOver with partitions that change dynamically depending on the expanded field(s).

Is there a method for this?

For context: this calculated field will be used eventually to calculate the contribution of the displayed field to change of Sales Revenue.

Thank you!

[abacon]

Hi @Aurelien,

Thanks for posting your question and welcome to the QuickSight community!

For my example below I’ve used Region, Subregion and Customer fields with a Sales total.

I first created a calculated field SalesAggregate as follows:
sumOver(sum(Sales),[Region,Subregion])

I then built a PivotTable as shown below (with Region collapsed):

Formatting the visual to show subtotals for Rows:

Then, when I expand the Region column I get:

And when I expand the Subregion column I get:

Let me know if this solves your question. If so, please mark this reply as solution. If not, I will take another look.

Many Thanks,
Andrew

[Aurelien]

Hello @abacon ,

Thank you for your answer.

The issue with sumOver(sum(Sales),[Region,Subregion]) is that the formula is static. The partitions do not change dynamically while expanding/collapsing fields.

I would like SalesAggregate to change its partitions based on expanded fields, similarly to what the standard Subtotal does.

Using your example:

• When only Regions are expanded, I would like the formula to be equivalent to:
  sumOver(sum(Sales),[Region])

• When Regions and Subregions are expanded, I would like the formula to be equivalent to:
  sumOver(sum(Sales),[Region,Subregion])
  with the expected result being your last screenshot.

I tried some sumOver formulas with ${visualDimensions} into the partitions, but it seems like I’m not using it appropriately and/or it’s not compatible with pivot tables.

I hope it’s clearer.

Thank you for your support!

Aurelien

[abacon]

Hi @Aurelien,

I’ve almost been able to recreate your example, I’ll try to explain my best solution.

I created 2 calculated fields that aggregate to the required level, i.e.
salesSumSubregion

sumOver(sum(Sales),[Region])

and
salesSumCustomer

sumOver(sum(Sales),[Region,Subregion])

Then I created a calculated field that chooses between the aggregations, depending on the pivot table visual aggregation:
salesAggChoice

ifelse(salesSumCustomer=sum(Sales),salesSumSubregion,salesSumCustomer)

Using the same pivot table as before and adding salesAggChoice to the values field well, with Subregion collapsed, I get:

Then, expanding Subregion, I get:

Which is almost what you need, but the subtotals for salesAggChoice are not as required.

One option maybe to prevent the Subregion subtotals from displaying and instead renaming salesAggChoice as the Subtotal column, e.g.

This is the nearest solution I can find so far, let me know if this helps.

Many Thanks,
Andrew

[Aurelien]

Hello Andrew,
It works! Thank you!

Below I am giving more details on how I implemented CTC in my pivot table. Quick remark though: It would be good that Quicksight Team works on adding a function that does the job for us. It is quite complex to implement at the moment, and quite heavy for QS as well.

Let’s assume I have 4 cuts now, e.g. Region, Subregion, Customer and Store. I also have 2 metrics: cw_sales and lw_sales, for current week and last week sales.

I would first create 4 functions:
lw_sales_cut_4

sumOver(sum(lw_sales),[Region,Subregion,Customer])

lw_sales_cut_3

sumOver(sum(lw_sales),[Region,Subregion])

lw_sales_cut_2

sumOver(sum(lw_sales),[Region])

lw_sales_cut_1

sumOver(sum(lw_sales),[ ])

I am then creating the function that is supposed to replicate Subtotal:
lw_sales_subtotal

ifelse({lw_sales_cut_4}=sum({cw_py_ordered_gms_eur}),
    ifelse({lw_sales_cut_3}=sum({cw_py_ordered_gms_eur}),
        ifelse({lw_sales_cut_2}=sum({cw_py_ordered_gms_eur}),
            {lw_sales_cut_1},{lw_sales_cut_2})
        ,{lw_sales_cut_3})
    ,{lw_sales_cut_4})

Eventually, I am creating the CTC formula:
sales_wow_ctc

(sum({cw_sales})-sum({lw_sales}))*10000/{lw_sales_subtotal}

Few issues that I was not able to solve:

• This method does not work when there is only 1 Store for 1 Customer, or 1 Customer for 1 Subregion. We have the example in Andrew’s screenshot with APJ > JAPN > AT&T. Subtotal should have shown 145.53 instead of 1,291.98.
• As mentioned above, this method is pretty heavy for QS. If you have other calculations in your dashboard (like mine), QS may error out.

Thank you Andrew for your support!