Create a Measure between related tables

[Rayouff]

Hi guys, 

I have a problem regarding a measure creation on Power Bi Desktop. I will illustrate my problem with an example : 

Let's consider a company headquarter that have 10'000'000$ of cash and invest X amount of $ into N stores. Moreover, every stores pay their employee a determined amount of $. Now the problem, the Company HQ wants to know the percentage of its 10'000'000$ that goes into every employee pocket. 

Example and then I will add some images, let's say that the HQ invests 1'000'000$ into store 1, then, HQ invests 10% into store 1. Now, Store 1 pays Employee N°1254 100'000$ thus, its represents 10% of Store 1 budget but, at a Company HQ level, it represents 1%. So, It is this final 1% (this "overall percentage") that I try to calculate but I cannot. 

It is important to note as well that I can only use Measures (I cannot modify any tables). Moreover, the three tables (Headquarter, Store, Employee) are linked with the following : 

Table Headquarter has Store ID and its investment into each store. The Store table contains each store ID and finally, in the Employee table, you have the store ID in which a specific employee is working and you have his salary.

Tables

[Rayouff]

Thank you, we start to have something intersting. I did not get an error but I got crazy result like 600% and so on. I have no right numbers.
It may come from the fact that I report each quarter (form "YYYY QQ") the situation. Do you know how I could incorporate this ? 

So that, for a specific quarter, we will have : 

2024 Q1 : Employee 1 100'000K, for an investment in store 1 of 1'000'000 from 10'000'000. Result expected 1%

2024 Q2 : Employee 1 100'000K, for an investment in store 1 of 1'300'000 from 16'000'000. Result expected 0.625%

The date are in a separate table linked to HQ General

[v-linhuizh-msft]

Thanks for the replies from johnt75.

Hi @Rayouff  ,

When not introducing dates, you can create a measure directly:

salary % = DIVIDE(MAX('Employee'[Salary]),SUM('HQ Value'[Investment in store]))

To add quarters, you can use the allexcept function to calculate the percentage for each quarter.

If there is still a problem, could you please provide more specific model fields, or a .pbix file link that removes sensitive data.

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[johnt75]

Try

Salary % of HQ =
SUMX (
    Employee,
    VAR StoreID =
        RELATED ( 'HQ General'[Store ID] )
    VAR StoreTotal =
        CALCULATE ( SUM ( 'HQ Value'[Investment] ), 'HQ Value'[Store ID] = StoreID )
    VAR Result =
        DIVIDE ( Employee[Salary], StoreTotal )
    RETURN
        Result
)

[johnt75]

Try

Salary % of HQ =
SUMX (
    Employee,
    DIVIDE ( Employee[Salary], RELATED ( Headquarters[Investment in Store] ) )
)

[Rayouff]

First of all, thank you for your answer. 

Secondly, It does not work because when I try it, I saw that I did a mistake when explaining my tables. Please see below a more complete view of my situation :