Group by summed values

Question & Answer
,
1

Hi:

I have a set of 3 options that can be chosen and my data is structured as:

Person 1 | Option A
Person 1 | Option B
Person 1 | Option C
Person 2 | Option A
Person 3 | Option A
Person 3 | Option C

Person 1 has chosen for option A, B and C, person 2 has chosen for option A and person 3 for option A and C.

I would like to create a table that shows all possible values. e.g.

Option A + B + C | count 1
Option A + B     | count 0
Option A + C     | count 1
Option A         | count 1
Option B         | count 0
Option C         | count 0

To accomplish this, I’ve created a calculated field on dataset level that assigns a binary value to each option.

Option A: value 1
Option B: value 2
Option C: value 4

Next I’ve created a secondary calculated field on the dataset level

sumOver(sum({option binary value}), [{person}])

But when I add this calculated field to as the Group By to a Table, I got the error “Custom aggregation fields is not allowed as a dimension”.

How would I be able to get the table I want?

– Ivan

2

sumOver({option binary value}, [{person}], PRE_AGG) should work. You are trying to add a post aggregation calculated field as a dimension. However, dimension are parsing before the aggregation. So, you got the error message. Force the sum to be calculated fore aggregation should work.

3

Hi, @Ivan_Eulaers. Did @Ying_Wang’s solution work for you? I am marking their reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!