5 Replies Latest reply on Jan 31, 2017 2:33 AM by robindra neogi

    Create a calculated measure that ignores filters (to act as a fixed denominator)

    robindra neogi



      I would like to  be able to set up a calculated measure where the numerator is selected using various quick filters, but the denominator is unaffected by any selections (but is itself defined by a calculation). The aim of this is to set up a data explorer where any measure can be selected for the numerator (using filters), but is standardised by a fixed denominator (in my example it is number of households in an area for the year in question).


      The structure of my data is a tall table, with only one column of values, and various columns of dimension that I use to filter the data. It contains a wide range of different data sources, but they all have two things in common, the data is held for local council area, and is for a specific financial year. A 'data source' and 'measure name' 'measure descriptor 1' 'measure descriptor 2' columns are used to identify the different data sources and measures contained within the table.


      I am able to use a calculated measure to create the denominator, but I do not know how to make it ignore filters. I have had some success by duplicating the data source and bringing in the numerator from one version and the denominator from another, but this seems to limit some of the functionality the calculated measures have (aggregations etc.). I have looked into level of detail calculations but not managed to get this to work yet.


      I basically want to replicate the functionality of PowerPivot, where a calculate measure will override any filter selection so long as those variables are defined within the calculation, but will respond to any filters not included in its definition.


      Many thanks