Create daily AVERAGE Measure by condition

[thlndlmngls]

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!

[v-xicai]

Hi @thlndlmngls ,

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.

[thlndlmngls]

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, 

[AiolosZhao]

Hi @thlndlmngls ,

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

[thlndlmngls]

Hello @AiolosZhao ,

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,

[AiolosZhao]

Hi @thlndlmngls ,

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

[thlndlmngls]

Hello @AiolosZhao .

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.

[AiolosZhao]

Hi @thlndlmngls ,

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

[thlndlmngls]

Hello @AiolosZhao 

Thank you for your reply.

I have tried the method below but it didn't work when I used filters of different periods like daily/weekly/monthly.

I suppose it is difficult to get information unless more detailed information is collected.

Best regards,

[AiolosZhao · Accepted solution]

Hi @thlndlmngls ,

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

Thanks.

Aiolos Zhao

[thlndlmngls]

Hello @AiolosZhao ,

Thank you for getting back quickly.

I will request further data to user and then may ask questions if I have. Thus, I am going to close this case.

Thank you for your coopertation!

Best regards,