Quick Sight Pivot wont show total & subtotal when using parameter

Q&A
, ,
1

image
image916×435 38.3 KB

I have this chart where I give filter using calculated field on the top so the user can choose which metrics they want to show in the chart, for some reason the percentage metrics wont show its grand total while the number metrics shows no issue.

When I tried to replicate the chart and directly using the field in the pivot chart instead of the filter field it show no issue.

Here’s my formula for the filter metrics selection:

ifelse(
    ${keyMetricsValueParam4}='Growth Revenue', {Growth Revenue},
    ${keyMetricsValueParam4}='Growth Transaction', {Growth Transaction},
    ${keyMetricsValueParam4}='Gross Margin', {Gross Margin},
    ${keyMetricsValueParam4}='Delta GM', {Delta GM},
    ${keyMetricsValueParam4}='Growth Item Sold', {Growth Item Sold},
    NULL
)

and here’s one of the formula of the percentage:

(sum(price)-sum({discount_price})-sum({new_cogs})-sum({new_first_mile_cost})-sum({new_processing_cost}))/(sum(price)-sum({discount_price}))
2

Found the issue, one of the metrics selection was followed by this formula:
(distinct_count({sales_id}) / lag(distinct_count({sales_id}), [{Date Series} ASC], 1, [{channel_name}])) - 1

I’m not sure why but the lag(distinct_count(meassure).. is not showing any numbers. Once I removed this metrics from my filter selection, the chart now working on the total to show the percentage number