Measure based on one or more slicer selection

Anonymous 

Thanks for that. It certainly helps, although it is difficult to provide useful advice without deeper understanding of the table and business structures.

The general consensus regarding best practices in data modelling is that you structure tables in two main categories: fact tables and Lookup or dimension tables, and it is highly recommended to have "single to many" relationships between these. This allows for an easier "filtering" process when it comes to measures and slicers.

For example, in your model, the "Dates" table and the "Target" or FCast" tables don't appear to be related, which makes it very difficult for example to compare actual sales with targets in a dynamic date period. In these case, what I do, is have a bridge "Period" table between the "Dates" table and the "Target" table for example, taking account the normally different data granularity intrinsic to each (which I cannot see in the image you have posted).

Let me put an example. Let's say your "Target" values are on a monthly granularity and your dates are on a Day granularity. I would set up a bridge period table above both these tables with a monthly granularity, including a YearMonth (integer) column common in all three tables, which I would set as the single -to -many relationship between your bridge period table to the target and dates table. This allows you to filter and create measures using this bridge period table, allowing you to have direct comparisons between sales and target data, and responsive to period slicers etc...

I would do the same with your country, category, client tables: create bridge tables with a common field which allows you to establish a single-to-many relationship which is what you then use for measures, slicers and visuals.

Here is an example:

(Apologies because the table names are in spanish). 

Basically I have a sales data table (Campañas) which has a day level granularity, and a Target table (Objetivos equipos) which has a month level granularity. The calendar table goes down to a day level granularity but I cannot connect this calendar table to my target table due to the different granularities (in order to avoid a many-to-many relationship). I have therefore created the Yearmonth bridge table which allows me to connect both tables with a single to many relationship in both, and this table then allows me to create measures which directly filter both the forecast table and the sales table to the same period. This is the table I the use as slicer and in visuals.

So...after all that, can you establish bridge tables in your model joining your target and sales table through single-to-many relationships? 

I hope I haven't made things more confusing….