Sales Calculation to ignore filters

[Anonymous]

Hi Experts

I am trying to work out a sales rate calculation that ignores filters. so here is the scenario.

I have 4 filters (Region, Country, Site and Department) the columns used for the filters come from 4 tables.

I am using the following DAX

Sales Y2 = CALCULATE([Sales],

ALL(PMS_PARTS[DEPARTMENT]),ALL(PMS_COMPLAINT[MANUFACTURING_REGISTERED_SITE]),All(PMS_COUNTRY[MDR_REPORTING_REGION]),

PMS_TM1_SALES_VOLUME[Fiscal Period] = "Y"

)

This formula works fine, it ignore the filter for Region, Site and Department, and gives me the correct sales for country UK £4m. 

This issue is when only UK is selected as a filter i would except my Sales Rate calculation to be = Divide(the number of issues/sales for UK). However, the above formula is giving me the sales assoicated with the individual issues / the sales for the issues (as opposed to the total UK Sales)

I want to be able to divide the issue by total sales..... 

[amitchandak]

@Anonymous ,

I did not get it completely . Need better examples and data.

 Sales Y2 = if( firstnonbank(table[Country],blank())="UK",Divide([Issue]/[sales]),CALCULATE([Sales],
ALL(PMS_PARTS[DEPARTMENT]),ALL(PMS_COMPLAINT[MANUFACTURING_REGISTERED_SITE]),All(PMS_COUNTRY[MDR_REPORTING_REGION]),
PMS_TM1_SALES_VOLUME[Fiscal Period] = "Y")
)

[Anonymous · Accepted solution]

Hi Amit

I worked the solution based on your help

Sales Y = CALCULATE([Sales],

ALL(PMS_COMPLAINT[REPORTED_FAILURE_MD_CD]),ALL(PMS_PARTS[PMS_FILE]),ALL(PMS_COMPLAINT[MANUFACTURING_REGISTERED_SITE]),All(PMS_COUNTRY[MDR_REPORTING_REGION]),

PMS_TM1_SALES_VOLUME[Fiscal Period] = "Y"

)

[amitchandak]

@Anonymous , Thanks for sharing.  Kudos!!