Ignore some filters from calculated metric

Question & Answer
, , , , , ,
1

In the table connected to the QuickSight dashboard, the number of our daily orders is recorded. It is also specified which supplier each order is related to. I calculate the number of orders as follows:

Number of Orders = Count({order_id})

I have written a formula that calculates the total share of orders as a percentage as follows:

Market share = percentOfTotal({orders})

An example of the table output using these metrics is as follows:

image

I have placed a filter next to the table so that users can filter and view the top X providers in the table.

When the user, for example, enters the number 3 in this box, the percentage share of the orders for those suppliers is calculated only among those 3 suppliers instead of the total. How can I fix this issue?

image

1 Like
2

Hello @MajidFa, if you want the calculation to ignore the filter for the visual, then you may want to switch from the percentOfTotal calculation to 2 LAC-W aggregations. This would allow you to utilize the PRE_FILTER calculation level on the countOver functions. It would look something like this:

countOver({order_id}, [{Providers}], PRE_FILTER)/countOver({order_id}, [], PRE_FILTER)

This should still return the actual percent of total, even after excluding some of the other rows. I will mark this response as the solution, but please let me know if you have any remaining questions. Thank you!