How to calculate measure, but ignore one filters' selection

[Anonymous]

Bear with me..

I am trying to get the difference between the lowest price and our own price measure created.

I want this to be like so:

Say for example the following

'Company', 'Price'.

A , 400

B, 100, 

ME, 150

Then measure would be -50

BUT if I use the slicer on DIM_COMPANY[COMPANY_CODE] to only show A and ME, then I want the value to be 250 (400-150).

However my current measure (below) ignores any slicer selection on the report page and still shows -50 (ie is still using B in the calculation).

I imagine this is because it removes the slicers selection of DIM_COMPANY[COMPANY_CODE], and replaces it with all companies except ME. I want it to retain selection of slicer, but always remove ME.

Total Diff to ME=
VAR __BASELINE_VALUE =
  CALCULATE(
  [Minimum Price],
  DIM_COMPANY[COMPANY_CODE] =  "ME" 
  )
VAR __MEASURE_VALUE =
  CALCULATE(
  [Minimum Price],
  DIM_COMPANY[COMPANY_CODE] <> "ME"
  )
RETURN

IF(
  ISBLANK(__BASELINE_VALUE),

  0,
  __MEASURE_VALUE - __BASELINE_VALUE
)

[prateekraina]

Hi @Anonymous,

KIndly acknowldege the solution provided and let other people know if you were able to get the resolution or not so that it could help others who are looking for similar solution.

Prateek Raina

[Anonymous]

Sorry for the delay! 

I must have missed the response email!

Have now done so, thank you so much for your suggestion 🙂

[prateekraina · Accepted solution]

Hi @Anonymous,

Use following measures:

Minimum Price = 
CALCULATE (
    MIN ( Table1[Price] ),
    FILTER (
        Table1,
        Table1[Company Code] <> "Me"
    )
)

Difference to Lowest = 
CALCULATE (
    SUM ( Table1[Price] ),
    FILTER (
        Table1,
        Table1[Company Code] = "Me"
    )
) - [Minimum Price]

Screenshots:

Prateek Raina

[Anonymous]

Worked perfectly! Thank you so much for this 🙂