How to calculate % share from the "Total"

[Phillip_Le]

I have a calculation for Impressions as sum(avg({total_impressions}, [monthending,{marketplace_id},placement])). I also have two dimensions: Video and Image. Let’s say the brand impressions between these two dimensions are approximately 50,000, with 40,000 from Video and 10,000 from Image. The total impressions from Video are 60,000 and from Image are 20,000. I want to calculate the brand impression share percentage for each from the total impressions. So, for Video, it would be 40,000/80,000 = 50%, and for Image, it would be 10,000/80,000 = 12.5%. How can I achieve this within QS?

The current issue is that I have those two dimensions showing. However, if I create a calculation for brand impressions divided by total impressions, QS is calculating it separately for each dimension.
i.e. Video - 40,000 / 60,000, Image - 10,000 / 20,000

Instead, I want the denominator to be consistent for both, which is the total of the two.

[Kristin]

Hi @Philip_Le Thanks for reaching out, and welcome to the QuickSight Community! I am pinging one of our SAs to make sure your question gets a reply on Monday if we don’t hear back from the community before then. Thx!

[Phillip_Le]

Thank you! Look forward to hearing back! @Kristin

[debapc]

Hi @Phillip_Le - did you try creating a calculated field as:
total_impression_all_modality = total_impression_video + total_impression_image.

Then calculating the brand impression share percentage as:

brand_impression_video_share = brand_impression_video / total_impression_all_modality
brand_impression_image_share = brand_impression_image / total_impression_all_modality

[Phillip_Le]

Total Impressions is calculated as sum(avg({total_impressions}, [monthending,{marketplace_id},placement])). How would I create one for Video and Image?

I’ve tried SumIf but there’s an error @debapc

[Phillip_Le]

@David_Wong Any thoughts on how this can be resolved?

[debapc]

@Phillip_Le - will you be able to share some sample data so that I can try out? Thanks!

[Phillip_Le]

Here is some dummy numbers,
On the left hand side these are the placements
Total Impression - sum(avg({total_impressions}, [monthending,{marketplace_id},placement]))
Branded Impression - sum(branded_impression)

For each placement I want the % share from that total ~59 billion, so that should always be the denominator

[debapc]

I believe you are displaying this as a Pivot table. Is this what you are looking for:

I used Percent of Total function on the second “Value (Sum)” to achieve this:

I applied Calculate as Table down:

Let us know if this is helpful. Thanks!

[debapc]

BTW, here is another view, where % of Branded Impression and Total Impression are calculated based on 59 billion.

My dataset (in your case, I assume some of the values are calculated):

I created 2 custom fields:

c_total_impression = maxOver(sumOver(sum(Value),[Impression])) => sumOver gives two sets of sum: 1/ sum for brand impression and 2/ sum for total impression, we want the sum for total impression for all the rows. Hence applied maxOver

Then created the percent field: c_percent = sum(Value)/{c_total_impression}

Finally used c_percent in the Pivot Table.

[Phillip_Le]

Total Impressions and Branded Impressions are values in my dataset - not a dimension field like how yours setup

[Phillip_Le]

@Phillip_Le

Also I have different markets and months

Also I have different markets and months.

Total Impression - sum(avg({total_impressions}, [monthending,{marketplace_id},placement]))
Branded Impression - sum(branded_impression)

For each placement I want the % share from that total ~59 billion, so that should always be the denominator

[debapc]

Hi @Phillip_Le - I have some questions on the data set. Will send you a private message