The calculated field I created cannot be used because "Custom aggregate fields cannot be used as dimensions"

[Natsuki]

I want to create the graph below from a dataset.（*The yellow shaded columns in the dataset are columns created for aggregation purposes and do not exist in the actual input.）
↓dataset

↓graph (x-axis: #Classification, y-axis: number of companies, color: PercentageOfWomen)

However, the calculated field I created cannot be set on the X-axis because of the error “Custom aggregated fields cannot be used as dimensions.”
Is there a good solution?

The details of what was carried out are shown below.

(1) I created a calculated field to calculate sales per person.
#SalesPerPerson

{Sales}/{NumberOfEmployees}

(2) I calculated percentile rank from sales per person.
#PercentileRank

percentileRank([sum({#SalesPerPerson}) DESC] , {Year})

(3) I set the classification based on percentile rank.
#Classification

ifelse(
{#PercentileRank}>=80,“E”,
{#PercentileRank}>=60,“D”,
{#PercentileRank}>=40,“C”,
{#PercentileRank}>=20,“B”,
“A”)

(4) When I try to set the last created category field to the X axis of a stacked 100% bar chart, I get an error saying “Custom aggregate fields cannot be used as dimensions.”

[yusukez]

Hi @Natsuki ,

Thank you for reaching out.

To conduct a functionality test in our environment, could you please provide us with the CSV file of the sample data you shared?

(By the way, I happen to be Japanese, so if you prefer communication in Japanese, please let me know and I can assist you in that language.)

BR

[ErikG]

Hi @Natsuki,

i tried something similar with different data and works fine. But only degis a calculated field.

If i use you data and the calculated fields i’m getting the same error. But i’m also not able to show your table as a QS table visual. Do you get the same error?

BR

[yusukez]

Hi @Natsuki ,

I replicated the scenario in my environment to mirror yours.

For the percentile rank to be computed accurately, please adjust the #PercentileRank as follows:

percentileRank([SalesPerPerson DESC], [Year], PRE_AGG)

Please note that QuickSight does not support the use of calculated fields with aggregation as a dimension group.
Instead, you must utilize the LAC-W (Level Aware Calculation) with PRE_AGG or PRE_FILTER to achieve row-level aggregation.

Result

Should you require any further assistance, please don’t hesitate to reach out.

BR

[Natsuki]

thank you.
I was able to solve the problem using the method you taught me!