I have a table where calculated field is showing value when collapse the pivot table. it aggregates table value instead of dividing by sum of colum2 / sum of colum3.
I have attached an example for clarification. Can anyone please guide me further for this problem?
Welcome to the QuickSight Community @na009 -
You should be able to accomplish this using table calculations. Please see the Exercise 12 in the Calculations section of our Author Workshop as an example.
Hello Kellie,
Thank you for your quick reply. That solution does not work for me. Its still giving me same error. The column which i am trying to fix is calculated field in the example column name is ratio ( value1/value2).
@na009 -
Are you getting that invalid calculation when turning on totals? Currently, when you apply totals it will only sum the column. We have identified a product feature request to allow more flexibility with total calculations. Today, if you want the overall ratio displayed for all years, you can create a separate visual and/or calculation to include it in your dashboard.
I am not using Total or subtotal functionality. I am using pivot table, collapse and expanding column. When I expand the view, the functionality of calculated field works correctly. When I collapse the view
aggregated value of sum of v1 and v2 works correctly. However Ratio column does not divide based on aggregated v1 and v2 column. Instead of it sums up entire expanded view data(incorrect result).
Hello Kellie,
Thank you for you response. I tried to use the same logic. It didn’t work on my end. Could you please confirm the calculation by collapsing the ID - 1, Does that produce 2.81 as a result for Ratio ID year Level?
When you de-expand Id- 1, the result should show below result. (I can not use Ratio ID level as a separate column, Can it product result as below?) + (ID) - 1, Year (ALL), V1 - 45, V2 - 16 , Ratio ID Year level - 2.81
@na009 -
Since I used the SUM function in the calculation, the pivot table visual aggregates that ratio value (by default sum). Which is why the correct values show at ID - Year level but aggregate to 22. xxx rather than calculate at the ID only level. Change the ratio calculation to use sumOver -
The sumOver forces the calculation over all the data. And even though we specify ID and Year in the partition dimensions, if Year isn’t expanded in pivot, it will do the correct calc at the ID level. You will see aggregation type of Custom in the Value field well for this calculated field ratio.
The pivot table should expand and collapse with the 2.81 for ID 1 and then the correct detail values at year level.
Hello Kellie,
Thank you for your reply. I am having an error stating, note my v1 and v2 column contains, NULL, ‘’ and 0. When I simply divide v1/v2, I get value as intended as soon as I write below query, I get error. How do i handle those values? below are my syntax
sumOver(sum({V1}),[ID,YR])/sumOver(sum({V2}),[ID,YR])
Hello,
I have figured out the solution.
RATIO ID YEAR LEVEL - sum(v1)/sum(v2)
PERCENTAGE_CHANGE -
percentDifference
(
sum({v1})/sum({v2}),
[{ID} DESC],
1,
[YR,ID]
)
