AVERAGEX Function PowerBI

[shreep1]

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

[shreep1]

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]

Hi,

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

[shreep1]

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?