Mismatched Aggregation Issue for Creating a Calculated Filed

Question & Answer
,
1

Hello team,

I am trying to create a Calculated filed to pull that in to Dimension for Pie Chart

Eg:- Mobiles = distinct_count(ifelse(type=‘Mobiles’,ID,NULL)
Laptops = distinct_count(ifelse(type=‘Laptops’ ,ID,NULL)
Laptops_Asia = distinct_count(ifelse(type=‘Laptops’ and {region} = ‘Asia’ ,ID,NULL)
Laptops_America = distinct_count(ifelse(type=‘Laptops’ and {region} = ‘America’ ,ID,NULL)
Ipads = distinct_count(ifelse(type=‘Ipads’,ID,NULL)

I am unable to drag all of these in to Measure Values to create a PIE chart as the Measure value is limiting to give only 1 field .So I tried to create all of these in to Single Calculated field but facing
Mismatched Aggregation, Custom aggregation can’t contain both aggregated and non-aggregated fields , My calculation field as follows

ifelse({type}=‘Mobiles’, {Mobiles}, {type} =‘Laptops’ and {region} = ‘Asia’ ,{Laptops_Asia},
{type} =‘Laptops’ and {region} = ‘America’ ,{Laptops_America},{type}=‘Ipads’,{Ipads},NULL)

Here two things

  1. I can directly drag my {type} in to Dimensions but problem is I can’t get the region specific counts as {type} column doesn’t contain region specific values . we have to put other filters to it but those filters shouldn’t apply to Mobiles & Ipads , So i left with creating custom calculated filed
  2. If I can drag Multiple measure values to a Pie chart just like Tableau , I can easily get the chart but its not allowing me to do the same. At a time I can drag only 1 agrregate filed to measure values.

Quick help is much appreciated.

2

From what I’m seeing it looks like you’re trying to do a pretty standard ifelse calculation. The issue I see is that you’re trying to set the resulting ‘then’ values as fields using curly braces, which won’t work. If you set them as strings instead and then add that calculated field into the pie chart you should be all good.

3

Hi @DeepikaChoda ,

The response from @nate.estrada is correct. Marking it as solution.
For added clarity - If you modify your calculation as follows, it will work.

ifelse( {type} = 'Mobiles', 'Mobiles', 
        {type} = 'Laptops' and {region} = 'Asia', 'Laptops_Asia',
        {type} = 'Laptops' and {region} = 'America', 'Laptops_America',
        {type} = 'Ipads', 'Ipads',
        NULL
    )

Regards,
Arun Santhosh
Pr QuickSight SA