How to group data in different buckets

I want to create a visual which shows me how my measure values are distributed (sort of like a histogram)

Use-case: I having different stores and for each store I’m calculating %compliance through a calculated field on runtime. Since number of stores are very large, it makes sense that I group them in some buckets instead of showing them individually. For example I’m thinking of creating 4 buckets i.e. <40%, 40-70%, 71-94% and 95-100% and show the store count corresponding to each bucket based on compliance measure output.

I can’t do this thing in dataset i.e. assign bucket to each store in dataset (pre-calculated), as the %compliance will change based on number of days user has decided to filter the data on, and other filter selections at run time.

Can you please suggest how can i go about implementing the same in QuickSight?

Does your filters in this use-case will update both numerator and denominator values for % calculation ? . can you share some sample dataset and output how you would like to visualize ?

@Ashok, My sample data can look like this:

Date Store ID Process compliance_flag
7/12/2022 1 cleaners 1
7/12/2022 1 restocking 0
7/12/2022 1 opening 1
7/12/2022 2 cleaners 1
7/12/2022 2 restocking 1
7/12/2022 2 opening 1
7/12/2022 3 cleaners 0
7/12/2022 3 restocking 0
7/12/2022 3 opening 1

There are like 18 different processes. I’m calculating compliance percentage as
sum(compliance_flag)/count(compliance_flag) for each storeID and Day.

So for 7/12, compliance scores become
Store 1 66%
Store 2 100%
Store 3 33%

Now since there is huge list of stores, I wanted a visualization like a bar chart which has some sort of buckets on x-axis, stores < 40% compliance, 40-71% , 71-94% and 95%+ and on Y-axis I show the count for each bucket. The user can select the date range for a single day or multiple days. In case of multiple days, I’m simply doing sum of compliance / count of compliance for that time period (not pre-aggregating at day level)

Hope this clarifies…

Hi, not sure if you were still looking for a solution, but let me know if the following works for you.

  1. Create a calculated field for Compliance %:
    sumOver({compliance_flag}, [{Store ID}], PRE_AGG)/countover({Store ID}, [{Store ID}], PRE_AGG)

  2. Create a calculated field for Compliance % Group:
    ifelse({Compliance %} >= 0.0 and {Compliance %} <= 0.3, ‘0%-30%’,
    {Compliance %} >0.3 and {Compliance %}<= 0.6, ‘31%-60%’, ‘61%-100’)

This should be all you need. You can create your bar chart and just add storeID to your Y axis settign the aggregation to Count Distinct.

Here’s my working sample:

Verify Compliance %:


Filtering on Date should work in the scenario.

1 Like