Feature Request - Table Row Calculations

Question & Answer
, ,
1

Hi,

I’m not sure if it is the right place to post this, but talking about myself and many others, we really need table calculation feature.

I Power BI and other tools, we are able to create report heads and do in table calculation.

Example:
Let’s assume I have Pivot table with reports heads like:

  • REVENUE
  • COST OF SALES
  • DIRECT AND OTHER INCOME
  • COMPENSATION COST
  • RENT
  • RECHARGES
  • OTHER EXPENSES

image
image836×170 5.44 KB

Now I can have a Pivot table and do the aggregation easily. I can see the sum of my revenue and other things. But when it comes to having other items which is not tagged or labeled in the data set, it will not be possible to do this.

For example, Gross Margin = REVENUE + DIRECT AND OTHER INCOME

  • COST OF SALES

EBITDA = Gross Margin - COMPENSATION COST - RENT - RECHARGES - OTHER EXPENSES

in Excel, Power BI, other BI Tools like SAP SAC, I can do this:

image
image910×456 21.3 KB

Is there any feature planned to support this, or do you have any solution for this?

2

Hi @haythoo,
Assuming that the underlying data model is structured exactly the way how you’ve shared in your images, you can use a calculated field to calculate simple derived KPIs on top of those contained in your dataset.

Matching your described example and formula, you could define Gross Margin as

ifelse({Report Heads}='REVENUE',{Actual CM CY},ifelse({Report Heads}='COST OF SALES',{Actual CM CY},ifelse({Report Heads}='DIRECT AND OTHER INCOME',{Actual CM CY},0)))

and a second calculated field EBITDA as:

{Gross Margin}+ ifelse({Report Heads}='COMPENSATION COST',-{Actual CM CY},ifelse({Report Heads}='RENT',-{Actual CM CY},ifelse({Report Heads}='RECHARGES',-{Actual CM CY},ifelse({Report Heads}='OTHER EXPENSES',-{Actual CM CY},0))))

If you add those as KPIs (summed up) to your dashboard, you will receive the values as shown on your screenshot.

table_row_calculations
table_row_calculations710×443 14.2 KB

Note that as of today, you cannot add/append new line items to your dataset within QuickSight (unless joined into the dataset). Depending on your exact use case, you could add those calculations into your ETL pipeline and/or consider adjusting your data model and pivot your table (so that revenue, cost of sales, etc. are individual measures on your dataset, which could be used in calculated fields to define and add the derived measures).

Did this answer your question? If so, please help the community out by marking this answer as “Solution!”. Thanks!

2 Likes
3

Thank you @Thomas

This what I’m doing exactly, but it will be in a deferent table, which is not a nice way to present data specially with readers ability’s to manage and add extra filters.

sumIf({Actual CM CY},
in({report_head}, ['REVENUE', 'COST OF SALES', 'DIRECT AND OTHER INCOME'])
)

image
image1468×393 52.7 KB

I hope QuickSight team consider this as a feature request and add it to the plan.

4

At AWS, our roadmap is primarily driven by our customers. Your feedback helps us build a better service. I have tagged this as a feature request.

1 Like