Dynamic Calculated field to find percentage of certain category

Question & Answer
, ,
1

Can I build a calculated field to find the percentage for a provided categorical dimensions. I have table with columns : DATE, LOCATION, AGE, TYPE, #UNITS.

I want to create the calculated field (say PCT_UNITS) to answer question like below, based on Filtering and Grouping dimension in visual instead of having to pre-define separate calculated fields for each combination dimensions

1- find the PCT_UNITS of certain TYPE by LOCATION?
2- find the PCT_UNITS with <40 days AGE by PERIOD?

level-aware #aggregation filters-controls-and-parameters

1 Like
2

Hello @tmpatil, welcome to the QuickSight community!

We should be able to create these calculations pretty easily in QuickSight, but let me know if you have any questions. There may be some variables depending on the way you are implementing them inside of your dashboard.

My data is a bit different than yours and I am not sure I fully understand how the 2nd option should work, but I created a demo in QuickSight Arena for both of the calculations you had in mind. Both allow for some user selections to manage the values being returned. Let me know if you have any questions:
Dynamic Calculated field to find percentage of certain category

3

Hello @tmpatil, I wanted to reach out since we have not received a response from you. Do you have any further questions on this topic or about the QuickSight Arena dashboard I shared with you? I believe the calculated fields I shared should guide you towards your expected output.

I will mark my response as the solution, but please let me know if you have any remaining questions and I can guide you further. Thank you!

4

Hi @DylanM thanks for getting back on the first question

What I am expecting to get is the same field PCT_UNITS should be able to answer both questions 1 and 2 depending on what’s in filtering and group by fields in the visual.

Thanks

5

Hello @tmpatil, well I don’t totally understand how those would work interchangeably in a single visual since our data is different, but you can definitely swap the return value with a parameter selection.

If you created a parameter that contains 2 values:
Type by Location
Age by Period

Then created a calculated field to check for the parameter value, you could return one value or the other.

ifelse(${Parameter} = "Type by Location", {PCT_UNITS (Type and Location)},
${Parameter} = "Age by Period", {PCT_UNITS (Age and Period - Dynamic)},
NULL)

The field names I am referencing above are from the Arena Dashboard that I sent in my previous response. Alternatively, if the calculations differ too much in how they are partitioned, you could use conditional rules to show 1 visual or another depending on the user selection.

Those are going to be the 2 options to manage this functionality.