Calculate the Average of values if filters are selcted else use the value for the whol

[Anonymous]

Hi,

I try to cerate a measure wich should calcualte the average number of the selected regions (via Chiclet Slicer).

If there is no filter set it should take the value of the whole country (CH - which is also part of this table).

In terms of logic, it should read something like this: If FilterIsActive take the average of median_R_mean of the selected regions else select the median_R_mean of "CH".

I have tried this : 

RE = IF(ISFILTERED(Region[Bezeichnung]), AVERAGE(COVID19Re_geoRegion_Last_value[median_R_mean]), FILTER(COVID19Re_geoRegion_Last_value, "CH"))

Unfortunately this approach does not work.

Can anyone help me on what possible approach I can follow here?

Thanks a lot.

[amitchandak]

@Anonymous , Try a measure like

RE = IF(ISFILTERED(Region[Bezeichnung]), AVERAGE(COVID19Re_geoRegion_Last_value[median_R_mean]), calculate(AVERAGE(COVID19Re_geoRegion_Last_value[median_R_mean]),FILTER(COVID19Re_geoRegion_Last_value, "CH")))

[Anonymous]

Hi admitchandak

Thanks for your help, now it works partly. It will calculate the average of the selected regions.

But for the else statement it does not work. There is no average calculation needed, only a filtering by CH (will show the value of the Row CH in this case 0.89).

But somehow it does not accept Filter (FILTER(COVID19Re_geoRegion_Last_value, "CH")).

Do you now how I can make it work? Thanks again

[v-yingjl · Accepted solution]

Hi @Anonymous ,

You cannot use the Filter() function directly in IF statement, basically the formula should be like this:

RE =
IF (
    ISFILTERED ( 'Table'[geoRegion] ),
    AVERAGE ( 'Table'[median_R_mean] ),
    CALCULATE (
        AVERAGE ( 'Table'[median_R_mean] ),
        FILTER ( 'Table', 'Table'[geoRegion] = "CH" )
    )
)

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.