Select not filtered items for a measure

[robitobi]

Dear Power BI Experts,

I have a situation where I want to get a measure for filtered values and another measure as reference which is consisting of all not filtered values.

Example:

In my data I have countries and a value. With a measure, I want to calculate a formula (as an example: the average) of the values.

Now I have a request that we have a slicer where I could select some values (e.g. DE and ES) and I want to get the measure (no problem) and a second measure which shows the average for all other not filtered values.

Result should be like this:

Measure for filtered values (this is no problem):

Measure2 for all other than the filtered values:

Reason is that we want to use the Measure as actual value in a KPI visual and Measure2 as reference value in the same KPI visual.

Do you have any idea how Measure2 needs to be defined so that it can be used?

Thanks in advance for any hint.

Kind regards

Robitobi

[FreemanZ · Accepted solution]

hi @robitobi 

try like:

measure1=

AVERAGE(TableName[value])

measure2=

CALCULATE(

    AVERAGE(TableName[value]),

    EXCEPT(

        ALL(TableName[Country]),

        VALUES(TableName[Country])

    )

)

[robitobi]

Thanks @FreemanZ , solution works!

Much appreciated!