Quicksight Calculated Field

Question

I am creating vizs in QS and need help with calculations. 
It is easier for me using PowerBI and DAX which I am more familiar with and can use formulas like ALL, FILTER, Sameperiodlastyear etc to get the results  I need,  but unfortunately are not available in QS or a way to translate them to equivalent QS formulas.

For example I need to make the sum of a numerical column static rather than the default dynamic aggregation and use as denominator in value calculations by groups
Tried the sumvalue function but the Prefilter, preagg and postaggfilter calculation levels are confusing.

I have attached sample data![excel table](/media/postImages/original/IM7myOmJbIQaqwt1g3Qij8nA)

These are some of the calculations I need to do and repeated for the other dimensions:

Total Amount by year, also by quarter
Total Quantity by year,  also by quarter

Companyshareamount by year= Amount by Companyandyear/ Total amount for that Year

Company amount PerYear
companyshareperyear percentage
companytransamountperQt_Yr
transamountperQt_Yr
companyshareperQt_Yr
Year = extract('YYYY', date)
Qtr_Yr

?companyshare= sumOver(sum(amount),[company], POST_AGG_FILTER)/sumOver(sum(amount), [])

?companyshareamountperQt for selected year = CompanyamountperQt/totalamountperQt

Company share for selected year- share for previous year

I have created year filters and parameters too 
Kindly help. Thanks in Advance

Accepted Answer

In Amazon **QuickSight**, you often need to use calculated fields to perform the same kind of data analysis that you would with **DAX** in **Power BI**. While the specific functions like ALL or `SamePeriodLastYear` don't have direct equivalents, you can often achieve similar results with **QuickSight** functions like `sumOver`, `avgOver`, `countOver`, etc., combined with the use of `PRE_FILTER, PRE_AGG`, and `POST_AGG_FILTER` level-of-detail controls.

#### Total Amount by Year and by Quarter:
```java
sumOver(sum(amount), [year], PRE_AGG)
```
> You would typically use the `sumOver` function, which calculates the total over a defined dimension or dimensions, without filtering out the current context.

For quarters, you'll need a calculated field that extracts the quarter from the date, and then use it in a similar `sumOver` function:
```java
sumOver(sum(amount), [year, quarter], PRE_AGG)
```

#### Company Share Amount by Year:
```java
sumOver(sum(amount), [company, year], POST_AGG_FILTER) / sumOver(sum(amount), [year], PRE_AGG)
```

#### Company Share Amount per Quarter for Selected Year:
```java
sumOver(sum(amount), [company, quarter], POST_AGG_FILTER) / sumOver(sum(amount), [quarter], PRE_AGG)
```

#### Company Share for Selected Year Minus Share for Previous Year:
```java
// Here you will calculate the company share amount for the current year
var currentYearShare = sumOver(sum(amount), [company, year], POST_AGG_FILTER) / sumOver(sum(amount), [year], PRE_AGG)

// Here you willl calculate the company share amount for the previous year
var previousYearShare = sumOver(sum(amount), [company, (year - 1)], POST_AGG_FILTER) / sumOver(sum(amount), [(year - 1)], PRE_AGG)

// Here you will subtract the previous year share from the current year share
currentYearShare - previousYearShare
```
> For these calculations, you'll need to ensure that `year` and `quarter` are recognized as dimensions in your dataset, and you'll need to create calculated fields that parse the year and quarter from the `date` field.

#### Note:
Regarding the `Prefilter`, `preagg`, and `postaggfilter` levels:

- **PRE_FILTER**: Calculations are made before any filters are applied.
- **PRE_AGG**: Calculations are made after filters are applied, but before aggregation.
- **POST_AGG_FILTER**: Calculations are made after aggregation, useful when you want to calculate percentages or differences after summing up the values.

#### Resources:
- [sumOver](https://docs.aws.amazon.com/quicksight/latest/user/sumOver-function.html)
- [sum](https://docs.aws.amazon.com/quicksight/latest/user/sum-function.html)
- [PRE_FILTER, PRE_AGG and POST_AGG_FILTER](https://docs.aws.amazon.com/quicksight/latest/user/sumOver-function.html#sumOver-function-arguments)

Quicksight Calculated Field

Total Amount by Year and by Quarter:

Company Share Amount by Year:

Company Share Amount per Quarter for Selected Year:

Company Share for Selected Year Minus Share for Previous Year:

Note:

Resources:

相關內容