Divide measure of different facts in a single table

[Anonymous]

Hi all,

I've been struggling with the following example:

Date	id_fact	id_client	id_product	id_employee	id_employeecontract	id_costcenter_client	id_costcenter_employee	Measure Count of Clients	Measure Amount of Acitivty Hours
1-7-2024	Accomodations	1	AccomodationA	0	0	CostcenterA	0	1
1-7-2024	Activities	1	ActivityA	Sarah	ContractA	CostcenterA	CostcenterX		3,5

I have a fact table with two different facts combined, accomodations and activies. Both facts have their own measure. 

The accomadtions fact has a count of the amount of clients, Measure A.

The activities fact has a count of the amount of hours, Measure B.

My goal is to combine the two as such so that I can make a new measure, which divides measure B with measure A, but keeping the filtercontext of the accomodation product and the employee info such as name, contract and employee costcenter:

Date	id_fact	id_client	id_product	id_employee	id_employeecontract	id_costcenter_client	id_costcenter_employee	Measure A: Count of Clients	Measure B: Amount of Acitivty Hours	Hours per Client (Measure B / Measure A)
1-7-2024	Accomodations	1	AccomodationA	Sarah	ContractA	CostcenterA	CostcenterX	1	3,5	3,5

I've been trying to use SUMMARIZE and TREATAS, but I got completely lost in there.

The example only contains one client, but the table contains multiple clients, accomodations and activites. 

Does anyone has a solution for this example?

Best regards

EDIT: Screenshots for better readability:

[Anonymous · Accepted solution]

Hi @Anonymous 

Please try this:

First of all, create 2 new calculated table:

_Activities = FILTER('Table','Table'[id_fact] = "Activities")

_Accomodations = FILTER('Table','Table'[id_fact] = "Accomodations")

Then add a table visual in the report view:

Best Regards

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