I have created a parameterized filter, but it is working fine on table level but do not working on KPI level. And Trying to give as many screen shot as possible to explain the problem
here is the dashboard I have
Just to let you know,
% total_with_A = sum(total_with_A) / sum(total)
Now I want to create a filter that will have three options, ‘All’, ‘A’, ‘B.’
‘All’ will include all the rows,
‘A’ will includes rows where ‘% total_with_A’ < 90%
‘B’ will includes rows where ‘% total_with_A’ >= 90%.
to do that I have created a parameter named as ‘filters’ , passed ‘All’ as static value. then add control, created a list as ‘All’, ‘A’, and ‘B’.
now created a calculated filed named as ‘filters_col’ using calculation below,
ifelse(
${filters} = ‘All’, 1,
${filters} = ‘A’ AND {% total_with_A} < 0.90, 1,
${filters} = ‘B’ AND {% total_with_A} >= 0.90, 1, 0
)
And then used this ‘filters_col’ as filter, and set the value equal to 1, please see the screen shot
now if you look at the filter configuration, aggregation option is set to custom (no other option given, and I think its because of
% total_with_A = sum(total_with_A) / sum(total)
if I do not use sum in that calculation it gives other options such as no aggregation or other .
However, I went ahead and applied the filter. Now if I choose ‘All’ it gives right values, (all the rows) on table and KPI reflect right as well.
If I choose ‘A’ then in the table it includes only rows where ‘%total_with_A’ is < 90%, which is expected, but do not make any change to KPI level, please refere to the screenshot.
As I choose ‘B’ from filter, it again work on the table, keeps rows where ‘% total_with_A’ >= 90% which is right, but in KPI level it says no data. please refer to screenshot.
How can this problem be solved ? Any suggestion will be helpful as I have spent huge amount of time on it without any success and now completely out of my depth.