Calculate % share of cost

Question & Answer
,
1

Hi there,

I am trying to calculate the % share of cost for each line. I have first calculated my total cost pre-filter so that whenever I apply filters, the total stay unchanged.

sumOver
(
** {total_cost},**
** [{campaign_id}],PRE_AGG**

and then calculated the %share of cost by: {total_cost}/{total cost pre-filter}

However, my total % is not matching 100%. How come?

)image

2

@Patrizia_Buompastor1 Quicksight has a function called percentoftotal, any reason you are not using that function while calculating % of share cost.

1 Like
3

Hi there @DeepakS ,

thanks for the quick help!

I want to only show the top 10 Campaigns (or those with >0 sales) and by using percentageoftotal, the % will be calculated on the filtered cost resulting to 100% but I need the percentage share to be calculated on the big total, despite the filter applied.

ialso tried to use Rank but this would not work because I need to apply a threshold and this can only be done by using a filter.

4

@Patrizia_Buompastor1, I do not see anything that could cause less than 100% share of cost, can you confirm if filter is causing some rows to be filtered and hence you do not see that totaling up to 100%. Another suggestion could be reducing the dataset to small set of rows (10-20 rows) and validate if the % share cost totals up to 100%.

1 Like
5

Hi @DeepakS,

whenever I have my campaigns filtered out to for example only the top 10, I want my share of cost to refer to the total cost of ALL campaigns, not only the top 10.

6

Do you want to show the Top 10 campaign ID and Cost. And also show the percentage of each Campaign versus total cost of all campaign?
Can see whether it is what you expected?

image
image603×567 44.1 KB

I have created 3 calculated field:

  1. Sales by Order ID (PRE_AGG): sumOver (Sales, [{Order ID}],PRE_AGG)
  2. Total Sales (PRE_AGG): sumOver (Sales,,PRE_AGG)
  3. %: sumOver (Sales, [{Order ID}],PRE_AGG) / sumOver (Sales, ,PRE_AGG)
2 Likes
7

Yes, that works! thank!!!

1 Like
8

Hi @royyung,

I would need your help again. While your solution worked perfectly in that case, I have another case where this application doesnt work.

First of all I have left joined two datasets to get (Total Cost Audience from Data1 and total cost slim from Data 2).
I want to calculate the % Share of cost: Total audience cost for each segment / total cost slim for the whole order.

Numerator: (sumOver({total_cost_audience}, [{order_id}, {line_item_id}, {segment_name}],PRE_AGG))
Denominator: (sumOver({Total Cost Slim_new}, [{order_id}],PRE_AGG))

the results look correct and so does the % of cost. However whnever I apply a Top / Bottom segment, the % changes.

I have to add that in order to get the Total Audience Slim New I had to so some data manipulation due t the fact as a result of the left join, some costs were duplicated.

Total cost slim new: ifelse(isNotNull({a_order_name}), ({total_cost_slim}/{distinct_count_segment}), {total_cost_slim})

image
image942×553 92.6 KB

9

@Patrizia_Buompastor1 can share some screenshots showing examples with numbers which one are correct and which one are not?
also, for integrity, can you please create another post rather than extending the question in the same post?
This is for other community members benefit, let them easier to search if they have the same questions.
Thanks!

1 Like