Hi,
I’m trying to calculat the % of each cell with total cost, but the cell value is already with calculated aggregation (filter). So when adding calcuation i get the below error.
Nesting of aggregate functions like percentOfTotal and PERCENTOFTOTAL(SUM(SUM(CASE WHEN “charge_type” = _UTF16’SavingsPlanCoveredUsage’ THEN “unblended_cost” ELSE NULL END) + SUM(CASE WHEN “charge_type” = _UTF16’Usage’ THEN “unblended_cost” ELSE NULL END))) is not allowed.
Is there any way to fix this.
Hello @naveenprasadshukla , welcome to the Quicksight community!
Could you provide an anonymized view of the calculated fields that you are using that is causing this error?
Hi Duncan,
Sorry for the delay in my reply…
Calculated field contain = OnDemand/sumOver(OnDemand) for % of Total.
OnDemand is sumIf({unblended_cost},{charge_type}=‘SavingsPlanCoveredUsage’) + sumIf({unblended_cost},{charge_type}=‘Usage’)
Even i used for PercentOfTotal but still no progress.
Thanks
Hello @naveenprasadshukla !
When you did sumOver(OnDemand) what did you partition it by?
could you try something like this instead?
sumIf(
{unblended_cost},
{charge_type}=‘SavingsPlanCoveredUsage’ OR
{charge_type}=‘Usage’
)
/
sum({unblended_cost})
Let me know if this helps!
Hey @naveenprasadshukla !
Did my post above help solve this issue, and if so could you mark my post as a solution to help the community?
Hi Duncan,
I tried with this method but getting the error as “The syntax of the calculated field expression is incorrect. Correct the syntax and choose Create again.” for charge_type
Regards,
Naveen
I will be more specific to what i’m looking at
| Sales | Value | Value in % |
|---|---|---|
| Asia | 50 | 24% |
| A | 20 | 10% |
| B | 30 | 14% |
| N America | 90 | 43% |
| A | 30 | 14% |
| B | 40 | 19% |
| C | 20 | 10% |
| S America | 70 | 33% |
| A | 40 | 19% |
| C | 30 | 14% |
| Grand Total | 210 | 100% |
Here I need the value in % which is incorrect in the quick sight, i’m not getting the grand total as 100%.
Hey @naveenprasadshukla !
Can you try this:
sumOver({Value}, [{Subregion}], PRE_AGG)/sumOver({Value}, , PRE_AGG)
Also, here I am using {Subregion} to match the table that you shared. I believe if you partition the sumOver calc by the smallest level in your fields it should work.
The calculation is not giving me the grand total of 100% but getting some where 60% only.
Hello @naveenprasadshukla !
Can you show me what you mean by only getting 60%? Do you mean that it is only returning 60% as the final value or that it is only totaling 60% of expected values?
Hello @naveenprasadshukla !
Are you able to provide more clarification on the error that you ran into?
Indeed, i’m getting only 60%
Please view the screen shot as the the blank value it is taking the calculation and the total now i see is 54% not the 100%
Hey @naveenprasadshukla !
When you created the calculated field, how did you partition the data to get to the most common attribute across the Business Service Name?
Let me share you the Arena link so that it will be more clear…
If you look at the Percent of Total is 100% and the calculation for each row is not correct.