Bar chart that shows totals grouped into categories?

Question & Answer
, , , , , ,
1

I have a table that lists company cars, column name of the car and column name of the company that owns the car. I put it in a horizontal bar graph where I have a list of companies and a total of cars for each company. Now I need the graph to show the total number of companies that have 1 car, the total number of companies that have 2 cars and so on. How to make?

1 Like
2

Hello @Bilck, welcome to the QuickSight community!

It sounds like you will need to build a calculation utilizing LAC-W aggregations in QuickSight. The only question I have is how many groups are you wanting to make? It is going to need to be a static list, so I would recommend grouping by more than 1 at a time if the number of cars per company scales exponentially.

The calculation I am thinking of would look something like this:

ifelse(
distinctCountOver({car}, [{company}], PRE_AGG) = 1, 'Companies with 1 Car',
distinctCountOver({car}, [{company}], PRE_AGG) = 2, 'Companies with 2 Cars',
distinctCountOver({car}, [{company}], PRE_AGG) = 3, 'Companies with 3 Cars',
distinctCountOver({car}, [{company}], PRE_AGG) = 4, 'Companies with 4 Cars',
NULL
)

Then you could use this as your x-axis and company name as your y-axis and use count distinct as your aggregation. I’ll mark this as the solution, but please let me know if you have any further questions or have issues implementing this. Thank you!

1 Like
3

Hi @DylanM.
Thanks for the answer.
It’s exactly what I needed.
I will study the function to understand how it works, but I applied it to the model you presented and I already got the result I needed.

1 Like