Hello,
I'm building a customer-facing embedded dashboard which has 5 tables. Each table is drawn from the same base table but showing different levels of detail. Each table is going to have a series of 'importance' columns, which are performance/success % type metrics but showing as a rank, where the rank is set by weighting the performance metric against a usage/volume field. The intention is to allow the end user to sort the table by a rank column of their choice, and therefore quickly see the most volume-weighted problematic data.
I've done this in the past, but it was very slow to manage - each one a separate SQL-based question, each having their own sets of field filters (there are lots as these dashboards are shared). So each time a new filter needs adding, I need to manually edit each table individually.
Is there a way of setting up this dashboard so that:
a) I don't have to edit each individual table - the filters used will be exactly the same, so I'd like to not have to maintain multiple tables. Ideally I would be able to just have access to the base table and just connect the fields in the usual way. So without this ranking business I could use a model.
b) But for weighting I need to have a separate set of 'totals' with which to create the weights. These are calculated on-the-fly depending on the date range used, for example, but also any other filters used. Typically I have done this by calculating the total per weight inside a cte, and cross joining that - I think this means I can't use Metabase Models, because I can't connect the cte to the date filters.
Any help appreciated!