cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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?

1 ACCEPTED SOLUTION
Memorable Member

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

Proud to be a Super User!

10 REPLIES 10
Community Support

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.

Frequent Visitor

Hello @v-xicai ,

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

Best regards,

Memorable Member

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

Proud to be a Super User!

Frequent Visitor

Hello @AiolosZhao ,

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.

Best regards,

Memorable Member

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

Proud to be a Super User!

Frequent Visitor

Hello @AiolosZhao .

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.

Memorable Member

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

Proud to be a Super User!

Frequent Visitor

Hello @AiolosZhao

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,

Memorable Member

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

Proud to be a Super User!

Frequent Visitor

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.

Best regards,

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors