Forum Discussion

Jerry1985's avatar
Jerry1985
Frequent Visitor
4 years ago
Solved

Average Ops Days.

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.

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

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

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    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's avatar
      Jerry1985
      Frequent Visitor
      4 years ago

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