Conditional Table level sum and percentile

Question & Answer
,
1

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.

image
image3076×586 241 KB

Example data in csv format:
bill_billing_period_start_date,line_item_line_item_type,line_item_blended_cost,account_name
2024-04-01T00:00:00.000Z,SavingsPlanCoveredUsage,10,dev-a
2024-04-01T00:00:00.000Z,Usage,20,dev-a
2024-04-01T00:00:00.000Z,SavingsPlanCoveredUsage,40,dev-b
2024-04-01T00:00:00.000Z,Usage,60,dev-b
2024-04-01T00:00:00.000Z,SavingsPlanCoveredUsage,30,qa-a
2024-04-01T00:00:00.000Z,Usage,60,qa-a
2024-04-01T00:00:00.000Z,SavingsPlanCoveredUsage,45,qa-b
2024-04-01T00:00:00.000Z,Usage,80,qa-b
2024-04-01T00:00:00.000Z,SavingsPlanRecurringFee,1000,org-root

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

2

Hi @ville.karkkainen ,

welcome to the community :tada:

You can find a short introduction including screenshots here:

Best regards,
Nico

3

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
ifelse(
    {line_item_line_item_type} = "SavingsPlanCoveredUsage",
    {line_item_blended_cost},
    0)
  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,
Nico

4

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.

5

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

  1. CALC_savings_plan_recurring_fee
ifelse(
    {line_item_line_item_type} = "SavingsPlanRecurringFee",
    {line_item_blended_cost},
    0
)
  1. CALC_savings_plan_recurring_fee_sum
sumOver(sum({CALC_savings_plan_recurring_fee}))
  1. CALC_savings_plan_recurring_fee_share
{CALC_savings_plan_covered_usage_percentage} * {CALC_savings_plan_recurring_fee_sum}
1 Like
6

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
sumOver(
    sum({CALC_savings_plan_recurring_fee}),
    [{bill_billing_period_start_date}],
    POST_AGG_FILTER
)
7

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.

8

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,
Nico

1 Like
9

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,
Andrew

10

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