Consider below are the columns in a QuickSight dataset.
MP GL CG Val new_SumOver_Val
1 G1 C1 20 447
1 G1 C1 40 447
1 G1 C1 50 447
1 G1 C2 89 447
1 G1 C2 98 447
1 G2 C3 90 447
1 G2 C3 60 447
We want to write a calculated field that will sum the ‘Val’ column. This new calc. field should consider filters applied on all other dimensions except ‘CG’ column. Kindly suggest.
In QuickSight, we are using below mentioned formula to calculate a SumOver Value.
Formula: SumOver({Val}, [{MP}], PRE_AGG)
The issue we are facing now is, the SumOver value is being affected by the filters chosen on ‘CG’.
@duncan I have tried using PRE_FILTER, but it is not working as expected. When the visual is filtered for more than one value in the ‘partition-by’ fields it is not working as expected.
The problem persists regardless of whether the filter is in visual or not in the visual. I have kept in the visual to make it appealing for the community.
Based on the information you have provided, I think the best way to handle this situation would be to remove the SumOverValue column from the table (since the value will display the same on every row if it is working appropriately), and display it in a KPI next to the table.
Then you can remove the filter that you do not want to apply to that specific value and make sure it is only utilizing the desired filter functionality.
What you said is right about the same values displayed in each row. This functionality is essential because the ultimate use case is utilizing this calculated field (SU_Calculation) as a denominator for a division calculation [CU_Calculation/SU_Calculation].
To elaborate, the numerator will encompass dimension-specific values for each respective row. Meanwhile, the denominator should consistently comprise the same values that are specific to the applied filters.
@Aravind are you trying to use the filtered values in a calculation? For example, filter the SU_Calc then take those filtered values and add them into a division calculation with CU_Calc?
Ultimately, because your SU_Calc has the GL field in it, if you filter a visual with by the GL field while using the calculation it you visual, it will always be affected.
I agree that when we apply filters to the fields listed in the Partition-by section of the formula (like the GL field), the SU_Calc values will naturally change. But the current issue is a bit different.
What I’m aiming for is to make sure that the calculated field remains unaffected by any filters applied to a specific column (Concession_reason) in the dataset. To achieve this, I’ve used the PRE_FILTER function as seen in the above image. This function is combined with partitioning by all other columns in the dataset.
The challenge I’m facing is that while this setup works well when all other columns are filtered for just one value, it doesn’t hold up when we filter for more than one value.
I hope I’ve explained the situation clearly. If possible, I’d really appreciate a quick discussion to brainstorm any potential solutions.
Hey @Aravind , sorry to get back to you so late! I think I found a potential solution for you thought after to digging into this more.
Can you take a look at this example from Demo Central as well as the QS community question it stemmed from:
I know that this may require you to change your visual to a pivot table but I think this will allow you to have your filter not affect multiple fields.
