RE : Subtotal % instead of sum of total %

Question & Answer
, , ,
1

Hi Team,
I am trying to figure out how to get the subtotal % in the aggregation (similar to excel). Is it possible in quicksight ?

excel wireframe
excel wireframe838×822 21.2 KB

2

Hi @parthprj ,
have you tried using a Pivot Table? Using pivot tables - Amazon QuickSight

This is something you can achieve with it:

image
image961×546 60.2 KB

Can you provide an example of input to obtain the output you are showing? :slight_smile:

Andrea

3

pivot
pivot2136×935 170 KB

Hi,
Yes so I am using pivot but it only shows me % of Total and not % of subtotal option which we get in Excel. Attaching the image what I have currently and above (excel pivot) is something I am trying to get.

4

I don’t know if I got it right @parthprj , sorry :slight_smile:

Do you want to obtain something like this?

image
image3200×1256 389 KB

Andrea

5

Yes but that should be % of subtotal and that subtotal should percentage of total. It’s a simple pivot subtotal % in excel pivots but in quicksight I dont see that option. I have attached the image of excel above. Let me know if we should offline on Slack or get on a quick call. Happy to walk you through the example.

6

I think that now I got what you are aiming to obtain.
Unfortunately, I think that at the moment is not possible to obtain that result in a single click, like you do in excel (at least I do not know a possible way to do it).

A suggestion can be using two levels of percentages, one local, one global, but that would mean having to different columns in the pivot, so I don’t know if this is an acceptable solution for you.

By creating two different calcualted fields:

  • Local: sum(quantity) / sumOver(sum(quantity), [{warehouse_id}, owner, {week_end_date}])
  • Total: percentOfTotal(sum(quantity),[{week_end_date}])

You will have the different calculation you want to obtain.
Also, if you create something like:

Final = ifelse(
  distinct_count({push_reason}) = 1,
  sum(quantity) / sumOver(sum(quantity), [{warehouse_id}, owner, {week_end_date}]),
  {Perc_Total}
)

you will also have empty cell in subtotals for the local calculation.

Still this is a workaround which I don’t know if it’s feasible for you.
This is an output example:

image
image1906×1178 258 KB

Andrea

1 Like
7

Hi @parthprj ,
can we select the last response as the solution and close the topic, or do you still need support? :slight_smile:

Let us know!
Have a nice one,
Andrea

8

Hi @parthprj,
Since we have not heard back in a while, I’ll go ahead and mark the solution. However, if you have any additional questions regarding your case, feel free to create a new post in the community and link this topic for relevant information if needed.

Thank you