Total() function alternative in QuickSight

Question & Answer
,
1

Hello team,

We have a toggle parameter in our reports which toggles between Numbers & Percentages based on our Dimension.

For the same , we wrote parameter logic like

ifelse(${Toggle}=“Numbers”,X,${Toggle}=“Percentages”, X/ total(X) *100,NULL)

This is not working as we don’t have total () Function in QuickSight , Could any one suggest an alternative for the same ?
I have tried sumOver(),sum(),percentOfTotal() . seems none of them working as expected.

Quick help is much appreciated.

2

Hi @DeepikaChoda
Sounds to me you are you the right path.
Do you have some sample data to check?
Did you create a calculated field just with sum() etc to see the calculation is right?
BR

3

Thanks @ErikG , Here our “X” refers to an aggregated field which calculates the distinct count and we want to show Percentages on Bars whenever we change Dimensions.

I tableau , we used to do X/Total(X) to get the desired output , But unable to debug the same in QuickSight

4

x = distinctCount(something)
Total(x) = sum(distinctCount(something))
is that right?
Looks strange. As distinctCount(something) is already a sum.

5

Yes, X/SumOver(X) worked for me.
In My Scenario it has to change according to the dimension I opt and segregate the result according to the Dimension Options.

6

Hi Deepika,

Above approach is okay if you will always have only one row per category.
If there can be multiple rows for each category, it is safer to wrap everything in aggregate functions as shown below.

ifelse(${Toggle}="Numbers",sum(X), 
    ${Toggle}="Percentages", sum(X)/ min(sumOver(X,[],PRE_AGG)) * 100,
    NULL)

The calculation level can be changed to PRE_FILTER if needed.

Regards,
Arun Santhosh
Pr QuickSight SA