Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

AVERAGEX Function PowerBI

Hello @Amit, @Greg , @tamerj1 , @lbendlin 

 

@amitchandak , @olgad , @Sahir_Maharaj , @FreemanZ , @tamerj1 , @Greg_Deckler 

 

I already have the Hour Clock from 0,1,2,3.....23 in the X-axis and the distinctcount of Jobs Produced in the Y-axis like this:

 

 

I want to add a Average Trend line here. I am writing the following DAX function to find the Average of the DistinctCount of Jobs Produced for the corresponding Hours like this:

 

Jobs at SOL DistinctCountNoBlank = CALCULATE(DISTINCTCOUNTNOBLANK(T_Cycle_Takt_Time_Detail[Job_No]),'T_Cycle_Takt_Time_Detail'[Workcenter_Code] IN {"3-R0"})
 
Y-axis in the above chart is the Jobs at SOL DistinctCountNoBlank
 
Now, I am trying to add the Average like this: 
 

Averagex SOL for Count of Jobs third method = VAR CJobs = CALCULATE(DISTINCTCOUNTNOBLANK(T_Cycle_Takt_Time_Detail[Job_No]),'T_Cycle_Takt_Time_Detail'[Workcenter_Code] IN {"3-R0"}) RETURN AVERAGEX('T_Cycle_Takt_Time_Detail', CJobs)

 

When I show this in the table, I get something like this:

 

 

There is no difference here between 'Averagex SOL for Count of Jobs third method' measure and 'Jobs at SOL DistinctCountNoBlank' . It is not giving me the Average Count of DistinctJobs for that corresponding hours. Not sure how to solve this problem?

 
Can you please help me figure out how to calculate the Average for those corresponding Hours? 
 
 
 

 

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    Anonymous , Use this measure using fx in constant line option

     

    AVERAGEX(Values(T_Cycle_Takt_Time_Detail[Hours]) ,CALCULATE(DISTINCTCOUNTNOBLANK(T_Cycle_Takt_Time_Detail[Job_No]),'T_Cycle_Takt_Time_Detail'[Workcenter_Code] IN {"3-R0"}))

     

    or use this as line

    CALCULATE(

    AVERAGEX(Values(T_Cycle_Takt_Time_Detail[Hours]) ,CALCULATE(DISTINCTCOUNTNOBLANK(T_Cycle_Takt_Time_Detail[Job_No]),'T_Cycle_Takt_Time_Detail'[Workcenter_Code] IN {"3-R0"})), allselected())

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hello, 

      amitchandak,

       

      @Amit, @Greg , @tamerj1 , @lbendlin 

       

      @olgad , @Sahir_Maharaj , @FreemanZ , @tamerj1 , @Greg_Deckler 

       

      Thanks for the input, but this is not helping either, it is yielding similar results like last time OR giving a totally different result like this:

       

       

      Averagex Custom 1 is for the 1st DAX above and Averagex Custom 2 is for the 2nd DAX above. 

       

      AVERAGEX(Values(T_Cycle_Takt_Time_Detail[Hours]) ,CALCULATE(DISTINCTCOUNTNOBLANK(T_Cycle_Takt_Time_Detail[Job_No]),'T_Cycle_Takt_Time_Detail'[Workcenter_Code] IN {"3-R0"}))

       

      The code above like you suggested is giving the same result which I have. 

       

      The 2nd code you suggested is giving the average value of 90.05 for all of the hours which does not make sense. 

       

      Do you have any other DAX code suggestion to address my concern?