Hi all, I have a pivot table where i do a count and decreasing count ( value and Percentage). When i do a subtotal, it just sums the value and not doing a percentage calculation. I also explored other options for total calculation such as sum, average, min and max but not a %calculation. Please let me know if there is a way to display % in subtotals.
Screenshot: In this case, i want to calculate % in subtotal whereas pivot table just sums up (3.00 + 1.57 = 4.57%)
Actual required value : ( 165/6954 = 2.49%)
Hello @Karthikeyan1, welcome to the QuickSight community!
How are you creating the percent value in your pivot table? If you build it in a calculated field, you may be able to get the expected value within your total. Since the pivot table is partitioned by date and category, you should be able to use a function that looks something like this:
Dec rate % = {Dec Count}/{total count}
And if that doesn’t look quite right, you could try adding in the sum aggregations as well to see if that helps:
Dec rate % = sum({Dec Count})/sum({total count})
In some scenarios adding or removing the aggregations for the fields can be beneficial. Let me know if that helps!
Hello @Karthikeyan1, since we have not heard back from you, I will mark my previous response as the solution. Please let me know if you have any follow-up questions on this, and I will be happy to guide you further. Thank you!
Thanks @DylanM.
I am calculating the % based on Count/(total count) but the total count is a calculated field and is a running sum by Date,Category.
So what i am expecting is for the table subtotal to calculate the % ( 165/6954) but instead it is just adding the records.
