How to show most common (mode) string in hierarchy?

I want to display string values (not COUNTs) for the highest level of my hierarchy.
Here is an example hierarchy: [Top, Middle, Bottom].
I also have a String field called Letter, containing either “a”, “b”, or “c”.
The relationship between the String and Bottom is 1:1 (one to one). For example, BottomA.Letter:"a". BottomB.Letter = "b", and so on. I hope that makes sense. (I am pretty new to databases.)

Next, I put my a Middle or Top level entity in a Pivot Table. However, the Visual display gives an integer such as 3 for how many unique string values there are, or how many counts of a string exist. However, I do not want to see this; I want to see the most common string.
For example, if a Middle, MiddleX was related to three Bottoms, BottomA1.Letter:"a", BottomA2.Letter:"a", and BottomB.Letter:"b", I would want the Pivot to display MiddleX.Letter:"a" since there are 2 "a"s and only 1 "b".
This is also known as a “mode” in statistics terminology. Please help me figure this out!

I have already referred to some similar posts: How can I calculate Mode (the most common value) in QuickSight? , How to count specific values in a string field


@anonymous can you share some screenshots?

1 Like

Here’s an example. My hierarchy is comprised of two levels: Server (1…7) > Host (1…4). There is a HotMetricAIO String field for each Host object. However, I can only set HotMetricAIO to display “Count” or “Count Distinct,” not the most common string value.

In an ideal world, the chart would look like this:

            | Nov 16, 2023        Nov 15, 2023         ...
            | HotMetricAIO        HotMetricAIO
Server1     |   metricname1         metricname2
    Host1   |     metricname1         metricname1
    Host2   |     metricname1         metricname2
    Host3   |     metricname2         metricname2
    Host4   |     metricname1         metricname2
Server2     |   metricname3         metricname1
    Host1   |     metricname3         metricname1
    Host2   |     metricname3         metricname1
...                                                     ...

Notice how the Server roll-ups contain the most prominent metric name (String-valued) for the respective Hosts. That is essentially the functionality I want to scale up.

My business logic already has the String for each Host, but I can’t seem to get QuickSight to visualize the actual String.

@anonymous ,

The pivot can display metric values . It is not possible to display string values (As I understand, in your case the “metricname1” is a string which has to be displayed ) .

Thank you for the quick response. Is there another Visual I can use to create the effect? Or any calculated field workarounds?

Hello @anonymous , @neelay , and @Koushik_Muthanna !

@anonymous were you able to find a solution for this or was @neelay or @Koushik_Muthanna 's suggestions helpful in finding a solution?

If so could you either post your solution or mark one of the comments above as a solution to help the community?

Hello @anonymous , @neelay , and @Koushik_Muthanna !

It has been some time since we have heard from you but we would still like to help you find a solution.

If there is not activity on this thread in 4 days this topic will be archived.