Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Create daily AVERAGE Measure by condition

Hello,

 

I am struggling with creating a measure of daily average by condition. Would you please help me with creating a measure.

 

Now I have data below.

 

I would like to calculate average hours when there are some projects with the same ID.

For example, ABC 125 has five projects.

 

 

Hours information is stored in a different table and there is another table of ID and hours association (no direct relationships between hours and project).

 

 

I have tried to use AVERAGE, FILTER and COUNT, but could not get results what I wanted.

Could you tell me what formula is required to calculate this figure? Or should I make a new column?

 

If you need more information, please let me know.

 

Thank you in advance!

 

Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Anonymous ,

     

    Yes, like you said, maybe if you can support more sample data, then I will try to figure out the issue.

    Thanks.

    Aiolos Zhao

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Anonymous ,

     

    Could you please also give a sample about how to mapping the hours with this table?

    You can create some fake hour data based on your example data now, just for showing your data model and relationship.

    Thanks.

    Aiolos Zhao

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hello Anonymous ,

       

      Thank you for reply.

       

      Here are the model of data about each table.

       

      And I would like to calculate like below. I tried to use AVERAGE in hours with COUNT of project but it didn't work.

       

       

      If you need more information, please let me know.

       

      Best regards,

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi Anonymous ,

         

        I think I got what you want:

        Measure = SUM(Table3[HOURS]) / CALCULATE(COUNTROWS(Table1),ALL(Table1[Project]))

        I think the key point to solve this problem is to get the same date between table 1 and table 3.

        If you can't get that, could you please give some sample with different date, so I can try to figure out it.

        Thanks.

        Aiolos Zhao

  • v-xicai's avatar
    v-xicai
    Community Support
    7 years ago

    Hi Anonymous ,

     

    Based on having created relationships between these tables as you said above, and set the Cross filter direction as Both, which will treat the these tables as a single table , then you can try to create measure like DAX below.

     

    Measure1 = DIVIDE (CALCULATE(SUM('Employee and hours'[Hours]),FILTER(ALLSELECTED('ID and Project'), 'ID and Project'[ID] =MAX('ID and Project'[ID]))),  CALCULATE(COUNT('ID and Project'[Project]),FILTER(ALLSELECTED('ID and Project'), 'ID and Project'[ID] =MAX('ID and Project'[ID])))) 

     

    Best Regards,

    Amy

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hello v-xicai ,

       

      Thank you for your reply.

       

      I have tried the measure below but it didn't work.

      I guess more information is required to show the total hours.

       

      Best regards,