Hi @sssaahi, welcome to the QuickSight Community, to address your issue of using LAC-W functions as dimensions in a pivot, you can follow these steps:

### Step 1: Remove Duplicates Using `avgOver`

First, you need to remove duplicates using the `avgOver`

function. Here’s how you can achieve this:

**Create a Calculated Field to Handle Duplicates:**Use the `avgOver`

function to create a calculated field that averages your measures over the dimensions causing duplicates.

```
avgOver({measure}, [{dimension1}, {dimension2}, ...])
```

For example, if you have a measure `Sales`

and dimensions `Product`

and `Date`

:

```
avgOver(Sales, [Product, Date])
```

This calculated field will average the sales values, effectively removing duplicates caused by different granularities.

### Step 2: Categorize Distinct Values into Percentile Buckets

Next, use the `percentileRank`

function to categorize the distinct values into 10 buckets:

**Create a Calculated Field for Percentile Rank:**

```
percentileRank([dimension], [measure])
```

For example, to calculate the percentile rank of sales for each product:

```
percentileRank([Product], Sales)
```

- **Convert Percentile Rank to Buckets:**Create another calculated field to convert the percentile rank into buckets. Assuming you want 10 buckets:

```
CASE
WHEN percentileRank <= 0.1 THEN '1'
WHEN percentileRank <= 0.2 THEN '2'
WHEN percentileRank <= 0.3 THEN '3'
WHEN percentileRank <= 0.4 THEN '4'
WHEN percentileRank <= 0.5 THEN '5'
WHEN percentileRank <= 0.6 THEN '6'
WHEN percentileRank <= 0.7 THEN '7'
WHEN percentileRank <= 0.8 THEN '8'
WHEN percentileRank <= 0.9 THEN '9'
ELSE '10'
END
```

### Step 3: Use Percentile Field in a Pivot Table

Finally, use the percentile field as a dimension in your pivot table to look at its distribution:

**Create the Pivot Table:**

- Drag the percentile bucket field (created in Step 2) to the Rows or Columns of your pivot table.
- Drag the measure (e.g., Sales) to the Values area.

**Customize the Pivot Table:**

- Adjust the pivot table to show the distribution of sales across the percentile buckets.

### Example Summary

Here’s a summary of the steps:

**Calculated Field to Remove Duplicates:**

```
avgOver(Sales, [Product, Date])
```

**Calculated Field for Percentile Rank:**

```
percentileRank([Product], Sales)
```

**Calculated Field for Percentile Buckets:**

```
CASE
WHEN percentileRank <= 0.1 THEN '1'
WHEN percentileRank <= 0.2 THEN '2'
WHEN percentileRank <= 0.3 THEN '3'
WHEN percentileRank <= 0.4 THEN '4'
WHEN percentileRank <= 0.5 THEN '5'
WHEN percentileRank <= 0.6 THEN '6'
WHEN percentileRank <= 0.7 THEN '7'
WHEN percentileRank <= 0.8 THEN '8'
WHEN percentileRank <= 0.9 THEN '9'
ELSE '10'
END
```

**Create the Pivot Table:**

- Use the percentile bucket field as a row or column.
- Use the de-duplicated measure (from Step 1) as the value.

By following these steps, you can successfully use LAC-W functions as dimensions in a pivot table.

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!