Benchmarking in Sisense
In my situation, using home health agency data, I needed to create benchmarks showing the data for the user's agency and the same data for all agencies for comparison, like average revenue per claim. We use data security to limit the data viewed by the user to the agency they belong to using the Agency Name field. This also constrained the benchmarks using the same formula's for "all" agencies. To get around this, I created a copy of the table containing the secured data. In the copy, the agency name is replaced with "Benchmarks", all other data is the same. For every field you want to use as a filter in a dashboard, you create a table of all possible values for that field and link that table to both the main table and the copy for the associated field. For example, for filtering on the Record Type field, you would create this table and link it to the original and the copy.
select distinct a.[record type] from [claims] as a
Your filter would then use the field record type from this table, which is linked to both the original and copy. This allows you to filter by the agency data and the summarized benchmark data on the dashboard when you want the benchmarks to change when the agency data changes. Make sure to set up data security to allow for the agency name of the particular agency and the value "Benchmarks". The user can then select "included all" for the secured field or select the allowed values and "Benchmarks"
