Quicksight Pivot Table Functionalities- Check

Q&A
, , , , , , , ,
1

Hi Team,

I have a Pivot Table in Quicksight in Hierarchical format with Subtotal’s Enabled. I have few concerns here. Please look at the structure of my Table. I have around 6 Dimensions ordered in Hierarchical way. Now the backend view is in Normalized format. I have around 7 to 8 categories are metrics .

All the categories and metric names are available as records in datset/mv and In quicksight I am doing the calculations to fetch the values. Now the Issue is, I have the lowest granularity level data aggregated in dataset level and I am using those store aggregated values to calculate the KPIs. for % metrics there are numerators and Denominator provided from MV side I am taking that to calculate the values. Now for Higher levels, we also have aggregated values at db level ie, Officer aggregated count, coop aggregated values, region aggregated values etc…for all dimension aggregated values present in rows. But we don’t need to use all of that . Instead we are using lowest level aggregated values and enabling subtotals. so higher level also calculated by pivot table.
But the problem is if we have a value for a partocular CoOP, for any KPI. It should be constant. so that we can compare that coop value with others. But since its a Pivot Table, whenevr we select any filter for any dimention under coop, the coop value also change

image
image1372×751 98.9 KB

For Example. The KPI value for Central district CoOp is the below when No filters are selected.

image
image1065×659 54.3 KB

Below is the value when we select any filters under coop/Central District.

image
image1000×727 43.6 KB

Can you help How can we achieve this functionality in Quicksight to make the subtotals constant without changing based on filters. We initially had to create a separate Grid only for comparison, But client wanted to do this comparison in Pivot table only, without changing the aggreagtions.
Also we do have few more concerns , please let us know how can we achieve this?
Based on the Initial Walkthrough of POC below are the feedbacks given by Jay. Please check and confirm if we need to incorporate anything else

  1. Add units for metrics (eg: %) suffix separately for each KPI

  2. Check for color coding of each category & metric names separately. (Headers Only)

  3. Conditional formatting for YOY metric that needs to be added from Backend in each Category, based on current year and previous year value

  4. Also check for the comparison feature to make it static (Do not change based on fitters for subtotals at higher levels)

  5. Also get rid of the manual calculation color coding approach for comparison with higher levels

2

Hi @Ganga,
With the way pivot tables are setup and function within Quick Sight, the subtotals are always going to reflect the data that’s available within it’s section, so if something is filtered out, the subtotal will reflect that.
You could try building a calculated field for your column field that incorporates a PRE_FILTER based on the field you’re filtering out; however that may not work across the board on the entire table since it’s specifying the level of aggregation for all rows, not just that section. I’m not familiar enough with the setup as a whole to say if this could work entirely but that would be the best option to test out.

In regards to your other questions:

  1. To assign different units of metrics within the same visual, you’ll need more than one field. When you assign metric units, they apply to all values of that specified field within a visual. You can assign a different unit for the same field in a different visual, but you can’t set different units to the same field’s values in one table.

  2. Separate color setting is not currently available for the headers

3. Not sure what the question is here regarding conditional formatting? Yes, you can create a calculated field and use that to handle conditional formatting for another field if that’s what you’re asking.

4. If you’d like to have a setup that’s static; you’ll need to setup a separate visual and either not apply the filters or make all calculations utilizing pre_filter.

5. Not familiar with what you’re referring to here, if you could elaborate

3

Hi @Ganga,

Just checking back in since we haven’t heard from you in a bit. I wanted to see if the guidance shared earlier helped resolve your question, or if you found a solution in the meantime.

If you still have any additional questions related to your initial post, feel free to share them. Otherwise, any update you’re able to provide within the next 3 business days would be helpful for the community.

Thank you

4

Hi @Ganga,

Since I haven’t received any further updates from you, I’ll treat this inquiry as complete for now. If you have any additional questions, feel free to create a new post in the community and link this discussion for context.

Thank you