Calculate average for rows not included in slicer selections

[chatals]

I'm working on a clinical trials study. I have a column "Symptom Progression Rate (SPR)" which is a numeric value that describes the speed at which an illness is progressing. For study participants, I'm trying to compare an average of that value, when filtered by a one or more demographic slicers such as gender, age, and ethnicity as well as clinical slicers that includes medications and nutrients, to the average SPR for participants who are not included in the slicer selections.

For example, out of 1,500 study participants, if I select 400 male participants between the age of 40-50 that are taking a particular drug, I want to calculate the average SPR for the 1,100 that are not selected by any of the slicers.

Model

DIM
Participant Table
-Participant ID
-Age
-Gender
-Ethnicity

(1:1)

Slope Table
-Participant ID
-SPR

(1:Many)
Drug Table
-ParticipantID
-Name of Drug

(1:Many)
Nutrient Table
-Participant ID
-Nutrient Name

[FreemanZ · Accepted solution]

hi @chatals ,

try like:

1. duplicate all dim tables and keep them disconnected, e.g

age2 = age

2.plot all slicers with columns from the dulpicate dim tables

3. wrap expressions with opposite condition like below in your meausre:

facttable[age]<MIN(age2[age])

    &&facttable[age]>MAX(age2[age])

facttable[gender]<>SELECTEDVALUE(gender2[gender])