Solved: Re: Average Ops Days.

Jerry1985 · ‎05-31-2022

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 · ‎05-31-2022

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] )
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Click here to visit my LinkedIn page

View solution in original post

Jihwan_Kim · ‎05-31-2022

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] )
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Click here to visit my LinkedIn page

Jerry1985 · ‎05-31-2022

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())))