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 hou...
Need Help
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?