Dividing 2 columns on a pivot table

Question & Answer
,
1

Hello everyone I am brand new to quicksight and need some guidance on what I believe is a really simple soulution and I am overthinking it on a pivot table.

I have been trying to create a calculated field to divide 2 fields but I keep getting an error that states “nesting of aggregate functions like SUM and SUM is not allowed”

I assume the error is from me having to create a calculated field to add a “count row” then got product total by doing a SUM of that new field and now trying to SUM(created total / sold ) to get % that was sold is causing an issue.

I tried using the add table calculation and percent of total however that just gave me 100% for every cell under that column which is not correct and could be due to again using a created column vs a source column since the created says (custom) vs the source says (sum)

Essentially my problem is

Customer - product total - number sold - % being sold (thats what im trying to determine and show)

Thank you for any guidance that can be provided

2

Hi Bryan,
Can you share screenshots of your formulas for better context? It sounds like you might need to use level aware calculations to achieve the correct level of granularity. The links below provide more context.

2 Likes
3

Hi @Bryan Can you share some more details about your query.

Thanks & Regards
Biswajit Dash

1 Like
4

@bergqdou @Biswajit_1993 Yes here are the formulas I used:

To make a row with a value of 1 and to count that row as a total I did the following:

Abs(1)

Sum({total})

The formula im using to try and divide the two which errors is:

Sum({total sales} / sum({sold}) )

5

@Bryan

It looks like you are nesting sums.

Do you want sum({total sales}) / sum({sold})?

Or {total sales} / sum({sold})?

1 Like