Average with two fact tables

[mscabrera]

Hi everyone!

Would like your help with the following problem. I have data from 2 fact tables that I think I can't combine.

Fact TR2: Contains the hours logged per day, by person and task in a project (project code), so there are multiple lines per code
Fact VW1: Contains data related to the project such as the region, country, project type and the  project code, here whe have also multiple lines per code, some of them can ha the same code, but different country or different project type.

Dim tab1: Contains the person and their role

What I'm trying to do is to calculate the average hours logged in a specif aggrupation, like this

The problem is that all i need for the average calculation is to have only the projects from Fact TR2, because there might be the case where we have project codes from TR2 that dont exist in VW1 table, and also projects that have hours logged in fact VW1 that are not in TR2 (I don't need the hours to calculate the average).
Since there's no way to create a relationship between the 2 tables and we can say that the "main" fact table should be TR2, from VW1 I need the hours logged and the task, the only way I came up to try to solve it was using the following measure:

But as you can see, when filtering the data (all filters of the report need to come from the Fact TR2 table) per project type I get the following results:

the expected outcome should be:

that is taking all hours logged and dividing them into the total number of codes, in this case for this specific project type, is 6.

The problem is that as far as I could undesrtand, the average is being performing the division only for the number of project codes that has hours logged in that specific task, in this case I clicked in Production hours logged from a SR, and for that specific one, there were 5 project codes with hours logged wich is equal to 58.

So I think the problem is to find a way to explicitly say to count all projects including the ones with zero hours logged, I thought I could use something that adds the removefilter function, and then reinstate it using the values() function, but since the data is coming from 2 different tables wit no relationship, I couldtn make it.
This calculation should also work when there are no filters applied, and continuosly divide the hours logged by the number of project codes that have hours FOR THAT SPECIFIC PROJECT TYPE, becuse from the result of that measure I need to perform other calculations that include constant numbers as show in the Matrix called Result were I used the Final Fixed totals measure, and work accordingly with all filter set that as I said before come from the fact TR2 table
I'm attaching a link to download a PBI file along with an excel file that has the dummy tables per reference.
Link: https://we.tl/t-cyaED0mIDR

Hope someone can help

Regards,

[amitchandak]

@mscabrera , You can try like

Calculate([Avg Meausre], filter( VW1, VW1[Code] in Values(VW1[Code]) )

You can also use treatas in place of filter

[mscabrera]

Hey @amitchandak , thank you for your response.

Unfortunatly the code yo provided doesn't  work as expected, in fact, gives the exact same result of the avg hours measure that I have

is not dividing the sum hrs into the total number of codes, that for this case is 6. 

The issue is that for that specific project type should be 6 but there are other project types as shown in following table

and I understand it might be related to the context transition, but at the end, I need to use the average measure to compute another total not grouped by task, but by project type and role:

The first table calcualte the average by task and role to provide a sum of hours logged by task and role

Hope you can help with it

Regards