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 was able to get the Average Jobs Per Hour Trend for each of the hour by writing this DAX code:

 

DAX Date Custom = AVERAGEX(VALUES(T_Cycle_Takt_Time_Detail[Op_Complete_Date].[Date]),CALCULATE(DISTINCTCOUNTNOBLANK('T_Cycle_Takt_Time_Detail'[Job_No]),'T_Cycle_Takt_Time_Detail'[Workcenter_Code] IN {"R0"}))
 
The code above displays this chart below:
 

 

X-axis and Y-axis is displayed like this in the above chart:

 

 Now, I am trying to find the Average Jobs per Hour for the month of April where I should be getting a value of 3.39 based on the trend line above. 

 

 

The code I wrote for the above display was:

 

DAX Date Custom Month = (AVERAGEX(VALUES(T_Cycle_Takt_Time_Detail[Op_Complete_Date].[Day]),CALCULATE(DISTINCTCOUNTNOBLANK('T_Cycle_Takt_Time_Detail'[Job_No]),'T_Cycle_Takt_Time_Detail'[Workcenter_Code] IN {"R0"})))

 

 DAX code above represent the Y-axis. 

 

X-axis is the Op_Complete_Date by Month hierarchy.

 

 

As you can clearly see, I am getting a value of Average Jobs Per Hour for the month of April as 65 which is clearly wrong and it should be 3.39. 

 

Can you please help me solve this problem by suggesting either fixing my DAX code, writing a new DAX code or using something else on the X-axis and Y-axis?

 

Thanks


 

 


 

 
 
 

 

 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

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

     

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

     

     danextian Ashish_Mathur Jihwan_Kim johnt75 Ritaf1983 

     

    On the 2nd graph of Monthly Trend even if I write the DAX code by mentioning: T_Cycle_Takt_Time_Detail[Op_Complete_Date].[Date]

     

    DAX Date Custom = AVERAGEX(VALUES(T_Cycle_Takt_Time_Detail[Op_Complete_Date].[Date]),CALCULATE(DISTINCTCOUNTNOBLANK('T_Cycle_Takt_Time_Detail'[Job_No]),'T_Cycle_Takt_Time_Detail'[Workcenter_Code] IN {"3-0030-R0"}))

     

    I still get the Average for Month of April as 65. 

     

    Even if I write this DAX code by including [Day] inside of a T_Cycle_Takt_Time_Detail[Op_Complete_Date].[Day]

     

    DAX Date Custom Month = (AVERAGEX(VALUES(T_Cycle_Takt_Time_Detail[Op_Complete_Date].[Day]),CALCULATE(DISTINCTCOUNTNOBLANK('T_Cycle_Takt_Time_Detail'[Job_No]),'T_Cycle_Takt_Time_Detail'[Workcenter_Code] IN {"R0"})))

     

    I still get the Monthly Average of 65. 

     

    If I write something like this:

     

    DAX Date Custom = AVERAGEX(VALUES(T_Cycle_Takt_Time_Detail[Op_Complete_Date].[Month]),CALCULATE(DISTINCTCOUNTNOBLANK('T_Cycle_Takt_Time_Detail'[Job_No]),'T_Cycle_Takt_Time_Detail'[Workcenter_Code] IN {"3-0030-R0"}))

     

    I get a Monthly Average of 1888 which is even more wrong.

     

    Not sure what DAX code to write to find the Average Jobs Per Hour for the month of April? 

     

     

     

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      3 years ago

      Hi,

      Share some data, explain the question and show the expected result.

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hello Ashish_Mathur 

         

        The main question is how to find the Average Count of Distinct Jobs Per Hour for each of the hours in the X-axis starting from 0, 1, 2,..all the way to 23, filtered by date. Y-axis is the Distinct Count of Jobs.

        Here is how the sample dataset looks like:

         

         

        When I try to find the Average Count of Distinct Jobs Per Hour for each of the hours filtered by different date ranges, I am not getting correct answer. For example, for the month of April, I am getting answers like 65 or 1888 which does not make sense. I should get something like 3.5 etc..

         

        Do you have any idea what DAX code shall I write to fix this issue?