Select not filtered items for a measure

robitobi — Wed, 01 Mar 2023 21:39:58 GMT

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

Re: Select not filtered items for a measure

FreemanZ — Thu, 02 Mar 2023 00:03:25 GMT

hi robitobi

try like:

measure1=

AVERAGE(TableName[value])

measure2=

CALCULATE(

AVERAGE(TableName[value]),

EXCEPT(

ALL(TableName[Country]),

VALUES(TableName[Country])

)

Re: Select not filtered items for a measure

robitobi — Thu, 02 Mar 2023 07:18:44 GMT

Thanks FreemanZ , solution works!

Much appreciated!

topic Re: Select not filtered items for a measure in DAX Commands and Tips

Select not filtered items for a measure

Re: Select not filtered items for a measure

Re: Select not filtered items for a measure