- Newest
- Most votes
- Most comments
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:
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:
sumOver(sum(amount), [year, quarter], PRE_AGG)
Company Share Amount by Year:
sumOver(sum(amount), [company, year], POST_AGG_FILTER) / sumOver(sum(amount), [year], PRE_AGG)
Company Share Amount per Quarter for Selected Year:
sumOver(sum(amount), [company, quarter], POST_AGG_FILTER) / sumOver(sum(amount), [quarter], PRE_AGG)
Company Share for Selected Year Minus Share for Previous Year:
// 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
andquarter
are recognized as dimensions in your dataset, and you'll need to create calculated fields that parse the year and quarter from thedate
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:
Relevant content
- asked a year ago
- asked 2 years ago
- AWS OFFICIALUpdated 2 years ago
- AWS OFFICIALUpdated a year ago
- AWS OFFICIALUpdated a year ago
Thank you very much!
sumOver(sum(amount), [year], PRE_AGG) (error msg says no aggregation when using pre_agg or pre_filter)
sumOver(sum(amount), [company, year], POST_AGG_FILTER) / sumOver(sum(amount), [year], PRE_AGG) (error msg says mismatched aggregation, can't have both aggregated and non-aggregated custom calculation together in any form)