Unable to Use Calculated PRT% Bucket as Dimension (Custom Aggregation Field Error)

Q&A
, , ,
1

Hi Team,

I need help with creating a PRT% bucket dimension in QuickSight for grouping machine counts by location.

My requirement

I want a pivot/matrix like this:

PRT% Bucket | Location A | Location B | Location C
---------------------------------------------------
0–30        |    X       |     Y      |     Z
31–50       |    X       |     Y      |     Z
>50         |    X       |     Y      |     Z

This requires the PRT% Bucket to be a dimension so I can place it in ROWS.

Current Data Structure

  • prtinsec (int)

  • statusasonist (timestamp)

  • Dynamic date parameters ${StartDate} / ${EndDate}

  • Multiple rows per machine per day

My Calculated Fields

1. Machine_PRT_Sec

sumIf(
    prtinsec,
    truncDate('DD', statusasonist) >= ${StartDate}
    AND truncDate('DD', statusasonist) <= ${EndDate}
)

2. Working_Day_Count

dateDiff(${StartDate}, ${EndDate}, 'DD') + 1

3. Machine_Working_Sec

maxOver(
    86400 * {Working_Day_Count},
    [{machine_name}]
)

4. Machine_PRT_Percentage

({Machine_PRT_Sec} / {Machine_Working_Sec}) * 100

5. PRT % Bucket

ifelse(
    {Machine_PRT_Percentage} < 30, "0–30",
    {Machine_PRT_Percentage} <= 50, "31–50",
    ">50"
)

Problem

QuickSight shows this error when I drag PRT % Bucket into Dimensions (Rows):

Custom aggregation field is not allowed as a dimension

Even though:

  • Machine_PRT_Percentage is aggregated inside the formula

  • I am using sumOver/maxOver scoped by machine_name

  • The bucket logic is simple ifelse

What I Need

I want to confirm:

  1. Is it possible to create a dimension bucket from an aggregated measure in QuickSight?

  2. If not, is pre-calculating PRT% bucket in ETL/Athena the only supported solution?

  3. Why do LAA functions (sumOver/maxOver) still result in the bucket being treated as a MEASURE?

  4. What is the recommended way to achieve group-wise bucketing (e.g., “0–30”, “31–50”) when the underlying value depends on aggregations + date parameters?

Any official guidance or best-practice patterns will be very helpful.

Thanks!

1 Like
2

Hello @Vaibhav.narwade, the key here is to use PRE_AGG within the sumOver/maxOver functions to avoid to custom aggregation error.

sumOver(
ifelse(
truncDate('DD', statusasonist) >= ${StartDate}
AND truncDate('DD', statusasonist) <= ${EndDate},
{prtinsec}, NULL), 
[{machine_name}], PRE_AGG)

Then, for the maxOver, try this:

maxOver(86400 * {Working_Day_Count}, [{machine_name]}, PRE_AGG)

We may also need to make some edits to Machine_PRT_Percentage and PRT % Bucket calculations as well, but this is the method you need to be using. It basically treats the field like it isn’t aggregated and it avoids custom calculation / aggregation errors. You just need to make sure that all fields being utilized match that functionality.

Let me know if that helps!