How to hide values from pivot table but keep as total?

so i have one calculated field as installment_type_aio


this gives me the columns for 0,2,3,6,9 and 12 installments. and if i add revenue as value it shows me each installment type’s revenue. i also added total at the end.

issue is this red metric. i want this red metric as total only. i dont want to see it under installment type. i want to add more values but keep them as single metrics only, dont want them for each installment types. how do i do that?

1 Like

Hello @baran, welcome to the QuickSight community!

Is there a reason you want to exclude the metrics from the table but still display them as a total? If I am going to be totally honest, this is not likely to ever function how you are expecting. Pivot tables are an excellent visual if the data returned is uniform and matches for each period column you are wanting to display.

My recommendation would be to display any of the total values that you want the user to be able to see that does not have the underlying data displayed in the visual within a KPI or alternate visual next to the pivot table. That way, you can still display the values you want to see, and you do not have to worry about it being manipulated by the pivot table visual or distorting the other data being shown. I hope this helps!

Hi @DylanM,
thank you for your response. Tbh i am not even sure what i want is pivot table but i am very new to this stuff.

So basically i have 5-6 metrics that is in my dataset, with product names, brand names and categories they fall you can understand from the image and post i am trying to understand how much revenue those items brought per installment type, and how many times this product page was displayed as a whole since it cannot be distinguished or separated by installment type. so if i add page displays (which is literally this red metric here) it shows same number for each installment type, but again it makes no sense because it belongs to product not the istallment type.

i can do it in “table” mode but this time it does not give me an option to quicklisy expand/collapse brands, categories etc. so in a sense i think i am trying to combine pivot table with an actual table if it makes sense?

1 Like

Hello @baran, so the page displayed field is the main issue causing problems with your display? What kind of a value is being returned by that field? I’m assuming it is an integer value.

Maybe you could consider adding that value to the Rows field well rather than the columns? That way it would only be aggregated by the brand names and category fields rather than the installment type. I am assuming the issue is being created by splitting it by installment type.

Hi again @DylanM,

Basically yes, but i will have 3-4 metrics too that i want to see like page displays. tbh i did not try adding them as rows because i want to see them on whatever “filter” (filter is the wrong word here but i forgot the english name, i’ll explain) i have selected in pivot. so if i collapse all, i want to see “Page Displays” metrics as brand level (pivot row is set to brand name in example image) if i expand by clicking “+” i want it to show individual categories, if i expand a category i want to see individual products etc. (again apologies because english is not my main language and i tend to forget terms)

i thought of making separate calculated fields and add them side by side but that way all i could make is “installment type 2” and rest, “installment type 3” and rest, which made the table worse. regardless, ill try to add them as rows and see.

unrelated note; i joined an aws quicksight training more than a year ago (thats why i forgot) but i am probably missing a very simple thing like creating a new calculated field and adding it as a value or something…

1 Like

Hello @baran, no worries at all! Honestly, dealing with some of these issues in Pivot tables will be one of the more difficult tasks in QuickSight. Once you start adding values into columns, QuickSight will immediately start trying to partition those values based on the groupings that have been added into your pivot table. Plus, everything will get duplicated based on groupings in the column field well. If you continue to have an issue accomplishing this, I think the best case scenario would be to build this in QuickSight arena. That way I could actually see how these data points interact with each other and possibly provide you with a solution.

Hello @baran, since we have not heard back from you, I will archive this question. If you still require assistance with this issue, please post a new topic in the community and link to this question to provide relevant information. That will ensure you are at the top of the priority list for a response from one of our QuickSight experts. Thank you!