Possible to use a value that ignores filters?

I'm looking for a way to use a value in a beast mode formula that ignores my current filters. I've been wrapping my head around this problem and can't think of another way to solve it. Here is an example with my issue:

Let's say my company has 5000 employees. There is a survey database with multiple rows of data for each employee. Some employees have completed multiple surveys, some no surveys, and some have completed 1 survey and didn't complete others. If I sort by unique employees, the table will look something like:

Unique Employees Not Completed Survey: 3000
Unique Employees Completed Survey: 4000
Total Unique Employees: 5000 (Using 'show total' Chart Properties option)

I can't display this data in a vacuum because it'll look like we have 7000 employees when we only have 5000. I also want to be able to show the % of employee who have completed any survey vs those who have completed none (4000 vs 1000 or 80% vs 20%).

What I think I want is to be able to do is pull a total unique employee count as a static variable while being able to bypass any filters (to always use 5000). Let's call it 'TotalEmployees.' This would allow me to create a percentage by placing my filters and allowing me to divide Count(Distinct)/TotalEmployees or Subtract: TotalEmployees-Count(Distinct) to show the number of employees who have not taken any survey.

Other solution options are welcome, but I've been working on this problem for a week and can't think of a way to get what I want and be able to display it in a chart form.

Best Answer

Answers