Conditional Table level sum and percentile

I’m working with AWS cost export data and want to allocate SavingPlanRecurringFee to AWS account groups in a proportion each account group is having SavingsPlanCoveredUsage.

The specific issues I’m having:

  • ‘SavingsPlanRecurringFee’ appears in dataset only in one row for each billing period. How to make that available to row level calculations specifically where ‘line_item_line_item_type’ is ‘SavingsPlanCoveredUsage’?
  • In order to calculate the percentage ‘SavingsPlanCoveredUsage’ I need to calculate sumOver ‘SavingsPlanCoveredUsage’ for each row.

Please see also attached screenshot from excel. There’s example cost export data (first 4 columns). Followed by some calculated fields (CALC-prefix). Finally pivot table shows the desired outcome.

Example data in csv format:

P.s. I created Dataset in Arena but I couldn’t figure out how to share that in the question.

Hi @ville.karkkainen ,

welcome to the community :tada:

You can find a short introduction including screenshots here:

Best regards,

Hi @ville.karkkainen

I tried to build a solution for your request in Arena.

Conditional Table

I created three fields:

  1. Only filter for SavingsPlanCoveredUsage : Cost for SavingsPlanCoveredUsage
    {line_item_line_item_type} = "SavingsPlanCoveredUsage",
  1. Sum the Costs for SavingsPlanCoveredUsage: SumOver SavingsPlanCoveredUsage
SumOver(sum({Cost for SavingsPlanCoveredUsage}))
  1. Calculate the percentage value: Percentage Value
sum({Cost for SavingsPlanCoveredUsage}) / {SumOver SavingsPlanCoveredUsage}

Was this what you are thinking about?

Best regards,

Thanks for your efforts, they we’re helpful and directed me to correct direction!

However there’s still one step to go. ‘SavingsPlanRecurringFee’ appear only once in dataset for each ‘bill_billing_period_start_date’ value (i.e. once for each month). Do you have any advice how to then use that value and calculate the sum for each account? i.e. Percentage Value * SavingsPlanRecurringFee.

I was able to get the desired results by adding the following three new fields:

  1. CALC_savings_plan_recurring_fee
    {line_item_line_item_type} = "SavingsPlanRecurringFee",
  1. CALC_savings_plan_recurring_fee_sum
  1. CALC_savings_plan_recurring_fee_share
{CALC_savings_plan_covered_usage_percentage} * {CALC_savings_plan_recurring_fee_sum}
1 Like

Also to take billing period in to account to correctly count recurring fee I modified the recurring fee as follows:

  1. CALC_savings_plan_recurring_fee_sum

Actually at least one thing still remains. In Analysis I need to be able to filter based on account_name (CALC_account_group). Now savings plan usage and percentage are calculated over the filtered rows. However, that’s wrong. Savings plan usages and percentages must always be calculated over entire dataset.

Hi @ville.karkkainen,

the issue is, that QS filters the account_name and then runs the sumOver function.
When using PRE_FILTER option in your calculation you can calc the sumOver function before filtering is done.

sumOver({Cost for SavingsPlanCoveredUsage},[], PRE_FILTER )

Best regards,

1 Like

Hi @ville.karkkainen,
I’m just checking in on this question, as we have not heard back from you. Did the response from @Nico help? If so, please mark his reply as solution, if we do not hear back in the next 3 days, we will archive the question.
Many Thanks,

Yes it helped and I’ve managed to create desired solution in QuickSight. Very positive feeling about this community support and especially big thanks to @Nico for being very helpful and quick in response!

1 Like