I would like to create a calculated field that ignores the filters of the dashboard.
Background
One of my calculated field is doing a division between “field A / field B” but when I apply a filter at dashboard level, both numerator and denominator are affected.
Solution tested
I tried using sumOver({total}, [], PRE_FILTER) for the field B but the value is largely inflated and it doesn’t correspond to the actual value
Is there a way to make “field B” ignoring the filters being applied to the dashboard?
sumOver({total}, [], PRE_FILTER) should sum all values of total in your dataset without any restriction. Is the formula meant to sum all values or do you need a partition? Could you create an example table with an explanation or upload a CSV to Amazon Arena?
It now shows the correct number but when I select some filters, the values changes. I’d like to keep the value constant despite the filtering applied to the dashboard.
Here is the link to Amazon Arena: Test1 (as this is the first time I use it, let me know if any other sort of permission need to be granted).
thank you for providing! The calculated field with field1-x is needed if you have these in your visual als well. In case you want the total over all field your sumOver function seems fine. Your Amazon Arena link helped here.
Unfortunately I cannot copy the dashboard, that is why I rebuild it.
I created your function in this dashboard, can you confirm this is the behavior you want? => SumOver
yes, that is the desired behavior. Unfortunately, I can’t copy the analysis either and I’m unable to check the details… Did you use sumOver({total}, [], PRE_FILTER) as calculated field? And then just applied regular filtering? In my case, using sumOver({total}, [], PRE_FILTER)returned a higher number (*1000) than the actual one as shown in the previously shared analysis.
yes, my formula looks like: sumOver(Profit, [], PRE_FILTER)
Basically your formula. That behavior with *1000 looks strange. Can you rebuild the sumOver visual with a new calculated field? Does the behavior continue?
I tried what suggested but the result is the same. Looks like the value is multiplied by 1,000 (the number of rows in the dataset). Guess this is caused by the dataset structure. Is there any way you can share the dataset you used?
SumOver is already summed up (34,006). If you sum every row it gets multiplied by the numer of rows. If you have exactly 1000 rows this results in 34,006,000.
Best regards,
Nico
Did my answer help you in resolving your request? If yes, I would request you to mark the post as “Solution”. This will help the community to find guidance and answers to similar question. Thank you!
Hi, @Ales Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the Quick Suite Community Q&A Forum!
