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 aver...
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

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hello Anonymous .

       

      Thank you for your reply.

       

      I have tried the measure but it didn't work when I put the measure into project table below. I guess it is a problem that Table 1 (Project) contains duplicated projects as it is sorted by date.

      This can be filtered by the time slicer by monthly/weekly/daily.

       

       

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

      As you mentioned, currently, there is no data about date in Table 3 (ID and employee).

       

      If you have any idea or suggestion, could you please let me know.

       

      Thank you.

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi Anonymous ,

         

        If you only want to add one dimension(Project), then you can try to use below method:

         

        Create a new column :

        Column = CALCULATE(COUNTROWS(Table1),ALL(Table1[Project]))

        Then create the measure 2:

        Measure 2 = SUM(Table3[HOURS]) / SUM(Table1[Column])
         
        Thanks.
        Aiolos Zhao