Average Ops Days.

[Jerry1985]

I am trying to create a measure for the following:

It takes total Sales / Ops days = Average daily Sales

My current measure for Ops days is as follows:

Ops Days = CALCULATE(SUM('PowerBI Data'[Total]),'PowerBI Data'[Revenue Reference]="OpsDay_",'PowerBI Data'[Actual / Plan]="Act",SWITCH('PowerBI Data'[Country2],"SG_",TRUE(), "MY_",TRUE(),"TH_",TRUE(),"PH_",TRUE(),"VN_",TRUE(),"ID_",TRUE()))

By country and by month the days calculated is correct. 

However when it comes to the final rollup. it becomes SG+MY+TH+PH+VN+ID. 

I am trying to get (Average(SG+MY+TH+PH+VN+ID) when the filters are not active.

[Jihwan_Kim · Accepted solution]

Hi,

Please try writing a measure like below, and replace the current one with the below.

fixed measure: =
IF (
    HASONEVALUE ( 'Country table'[Country column] ),
    [Your Ops Days measure],
    AVERAGEX ( VALUES ( 'Country table'[Country column] ), [Your Ops Days measure] )
)

[Jerry1985]

I ended up using the back syntax as i needed the filters to calculate when other filters were active too. 
I used this syntax and it works 🙂 thanks!!

AVERAGEX(VALUES('PowerBI Data'[Country2]),CALCULATE(SUM('PowerBI Data'[Total]),'PowerBI Data'[Revenue Reference]="OpsDay_",'PowerBI Data'[Actual / Plan]="Act",SWITCH('PowerBI Data'[Country],"SG",TRUE(), "MY",TRUE(),"TH",TRUE(),"PH",TRUE(),"VN",TRUE(),"ID",TRUE())))