Percent of Total Calculation Breaking "Other" Grouping Functionality

Q&A
,
1

Hello - I’m using the standard Sales Pipeline dataset with a single calculated field called “Percent of Total Calculation” as seen below:

percentOfTotal(sum({Weighted Revenue}), [])*100

The first chart below displays as expected, with the total summing to 100%. In the second chart, I limited the number of segments to 3 in the “Group/Color” menu, attempting to show the top 3 for each segment and then the rest being grouped into the “Other” category. However, because I’m using a table calculation - Quicksight doesn’t seem to recognize the “Other” category, despite showing it in the legend.

Assuming this isn’t a bug of some sort - I tried to find a workaround using ranks and custom grouping, but I run into a “Custom aggregation field is not allowed as a dimension” error using this path. Thanks in advance for any asssitance.

image
image807×559 57.1 KB

2

Hi @erweinstein,
What if you tried something like the following:
Create a ranking for your salespeople based on the segments (or however you’d like to handle the aggregation).

Something like:
denseRank(
[Sales DESC],
[Segment],
PRE_AGG
)

Then create an ifelse that handles the ‘Other’ section:
ifelse(
{Country Rank per Segment} < 4, Country,
'Other')

Then use the ifelse field in your group/color and you should get your top 3 sales shown with other’s being placed in an other category.

Let me know if this works for your case or if you have any additional questions

3

Hi Brett - the rank formula you shared doesn’t work as I’d expect and instead I get ranks that are in the hundreds for each salesperson within each segment. The way I get the ranks to work is by adding a sum aggregation to the revenue and not doing a PRE_AGG:

denseRank(
  [sum({Weighted Revenue}) DESC],
  [Segment]
)

However - then the ifElse calculation gives me a mismatched aggregation error when I try to use it as a group/color field. Thanks in advance for any additional help.

4

@Brett - just checking in if you have a solution for this, thanks!

5

Hi @erweinstein,

Could you share what the outcome of the rank I provided and how it differs from what you’re expecting, I’m not sure what you mean by providing ranks in the 100’s..are there no smaller numbers?
The mismatched aggregation scenario is why I suggested looping in PRE_AGG, as you’ll need to do something along these lines to avoid your error.
Can you provide a small example of how your data is setup, maybe I’m missing something in how your data is aggregated.

6

Hi @Brett - I believe the problem is that there are hundreds of rows in the dataset where each salesperson appears under each segment (e.g. there are 533 “Anne Smith” rows with revenue under the “Enterprise” category) - so if I use a PRE_AGG, we’re not seeing the actual sum of the revenue field across each segment/salesperson combination.

image
image1013×266 23.9 KB

7

Hi @erweinstein,

Check out the attached article below, it dives a lot deeper into scenarios that are common to yours. Let me know if this helps with your case:

8

@Brett I created an Arena dashboard to showcase what I am experiencing.

Aggregated Rank with Other Group

Graph 1 appears to be a bug. When using a calculated field with a table calculation - the grouping mechanism does not sort into an other category as expected. Even stranger, it shows the “Other” group in the legend, but there are no other values displaying in the graph.

Graph 2 shows how the PRE_AGG rank wouldn’t work as expected and Graph 3 shows the rank working as expected when aggregated, but I’m still unable to get the ifElse calculation to work after reviewing the link you shared pertaining to mismatched aggregation.

Hoping you can confirm whether or not the behavior in graph 1 is expected, and if it’s not a bug, how I can get this to work properly. Thank you for your help.

9

@Brett - any luck with the Arena analysis?

10

Hi @erweinstein, Thank you for sharing. I tested out a few things but it’s going to get around the mismatched aggregation issues in this scenario. What if you tried creating the ranking calculation on the dataset layer, that may allow you to bypass the mismatched aggregations

11

@Brett - will give that a shot. Any thoughts on the Quicksight behavior when trying to use the grouping functionality? Why does it not work as expected with table calculations?

12

as a note, we can’t use ranking calculation on the dataset layer as we need post-filter rankings applied, which also have varying partitions based on a user selected parameter toggle value. We can reach out to our AWS contacts for further investigation and potential confirmation of a bug that could be fixed in future on AWS side