Replicate Calculated Fields from Tableau to Quicksight using FIXED LOD

[chabbils]

Hi all,

I replicated below mentioned 2 calculated fields from Tableau to QS but the values under Total are not matching (whereas the values against each GL_Family are matching). Can you please help me troubleshoot the issue?

Calculated Fields in Tableau:

• Site Mix Fixed:

IF [MetricSelector] = "Units" THEN
{ FIXED [Year],[calendar_month_of_year],[marketplace_id] : SUM([site_units])}
ELSE
{ FIXED [Year],[calendar_month_of_year],[marketplace_id] : SUM([site_ops_usd])}
END

• Mix Fixed:

IF [MetricSelector] = "Units" THEN
SUM([site_units])/SUM([Site Mix Fixed])
ELSE
SUM([site_ops_usd])/SUM([Site Mix Fixed])
END

Calculated Fields in Quicksight:

• Site Mix Fixed:

ifelse
(
${MetricSelector}='Units',
sum({site_units}, [{reporting_year},{calendar_month_of_year},{marketplace_id}]),
sum({site_ops_usd}, [{reporting_year},{calendar_month_of_year},{marketplace_id}])
)

• Mix Fixed:

ifelse
(
${MetricSelector}='Units',
sum({site_units})/sum({Site Mix Fixed}),
sum({site_ops_usd})/sum({Site Mix Fixed})
)

Regards,
Chabbil

[David_Wong]

Hi @chabbils,

Can you check what aggregation you’re using on your Site Mix Fixed field in the Value field well? It looks like your total is showing the max. If you change it to sum, I think it should work.

Regarding the total for Mix Fixed, do you not want the percentages to add up to 100%?

[chabbils]

Hi @David_Wong, I am using SUM in the Value field well for Site Mix Fixed. PFA the screenshot below.

Regarding the total for Mix Fixed, I do not want the percentages to add up to 100%. I want the value to be same as Tableau.

[chabbils]

Hi @David_Wong, @Krisztian_Nemeth @DylanM @Sanjeeb2022, can you please look into this and provide your inputs?

[DylanM]

Hello @chabbils, I think you will want to use the sumOver function instead of the sum function.

sumOver - Amazon QuickSight

I think it would be something like this:

Site Mix Fixed = 
ifelse
(
${MetricSelector}='Units',
sumOver({site_units}, [{reporting_year},{calendar_month_of_year},{marketplace_id}], PRE_AGG),
sumOver({site_ops_usd}, [{reporting_year},{calendar_month_of_year},{marketplace_id}], PRE_AGG)
)

Mix Fixed = 
ifelse
(
${MetricSelector}='Units',
sumOver({site_units}, [{reporting_year},{calendar_month_of_year},{marketplace_id}, {gl_family}], PRE_AGG)/{Site Mix Fixed},
sumOver({site_ops_usd}, [{reporting_year},{calendar_month_of_year},{marketplace_id}, {gl_family}], PRE_AGG)/{Site Mix Fixed}
)

If it gives you issues for using {Site Mix Fixed} in the 2nd function, I would replace it with the actual sumOver functions from the first calculated field rather than using Site Mix Fixed as the denominator.

I will mark this as the solution, but if you have any further questions, please let me know!

[chabbils]

Hi @DylanM , I tried the way you suggested but didn’t achieve the desired result. Below are the values shown based on the new fields created (i.e. Site Mix Fixed 1 and Mix Fixed 1). The values are way off than the derived result. Please take a look and suggest what can we do next?

Also, please unmark the above response as Solution.

[DylanM]

Hello @chabbils, I am thinking we will likely need to change the partition fields that are being used in the calculations. It seems like my suggestion to aggregate down to month was incorrect, so we will just partition by year. I’ll post updates that I think might work.

Site Mix Fixed = 
ifelse
(
${MetricSelector}='Units',
sumOver({site_units}, [{reporting_year}], PRE_AGG),
sumOver({site_ops_usd}, [{reporting_year}], PRE_AGG)
)

Mix Fixed = 
ifelse
(
${MetricSelector}='Units',
sumOver({site_units}, [{reporting_year}, {gl_family}], PRE_AGG)/{Site Mix Fixed},
sumOver({site_ops_usd}, [{reporting_year}, {gl_family}], PRE_AGG)/{Site Mix Fixed}
)

These partitions seem to be more closely aligned with your table. Please mark as a solution if these updates give you the desired result. Also, you can try adjusting the partition fields of the functions if necessary.

[chabbils]

Hi @DylanM , I tried the above mentioned way but didn’t achieve the desired result. Below are the values I get based on the above mentioned way (i.e. Site Mix Fixed 1 and Mix Fixed 1):

I also tried adjusting the partition fields of the functions as follows but no luck.

Site Mix Fixed = 
ifelse
(
${MetricSelector}='Units',
sumOver({site_units}, [{reporting_year},{calendar_month_of_year}], PRE_AGG),
sumOver({site_ops_usd}, [{reporting_year},{calendar_month_of_year}], PRE_AGG)
)

Site Mix Fixed = 
ifelse
(
${MetricSelector}='Units',
sumOver({site_units}, [{reporting_year},{calendar_month_of_year},{marketplace_id}], PRE_AGG),
sumOver({site_ops_usd}, [{reporting_year},{calendar_month_of_year},{marketplace_id}], PRE_AGG)
)

Can you please take a look and suggest next steps? I’m not sure if there is a way to get the values under Total the way I want.

[DylanM]

Hello @chabbils, my apologies for the delayed response. I know there can often be some difficulty getting the totals to display accordingly when you are totaling calculated fields in a pivot table. I know it isn’t perfect in terms of the visual representation, but an alternative that usually works a bit better would be to display totals in KPIs outside of the pivot table. That way you can aggregate them a little differently and it is easier to target the specific values you are looking for.

[DylanM]

Hello @chabbils, since we have not heard back, I will archive this topic. If you have further questions on this issue, please create a new topic in the community and link to this question so we can provide further support. That will ensure you are at the top of the priority list for a response from one of our QuickSight experts. Thank you!