Divide values in other table proportionally

[GeorgW]

Hello,

I have two tables, "tbl_fte" and "tbl_salary_costs".

tbl_fte:

tbl_salary_costs:

In the "tbl_fte" i enter the distribution of the fte (full time equivalents) of the employees. So John Doe has a total of 1 FTE, Johnny Depp also, Brad Pitt only a total of 0.5 FTE.

Now the salary costs from the other table should be assigned to the corresponding departments.

The correct calculation would therefore be as follows:

Is there a measure that can help me to do this?

Thank you very much!

[Anonymous]

HI @GeorgW,

I think you need to add a calculated column to use current peronal_nr id to lookup corresponding amount and calculate with the current fte percent of total fte.

Salary_cost =
VAR costs=
    CALCULATE (
        SUM ( tbl_salary_costs[salary_cost] ),
        FILTER (
            tbl_salary_costs,
            tbl_salary_costs[personal_nr] = EARLIER ( tbl_fte[personal_nr] )
        )
    )
VAR ftePercent =
    tbl_fte[fte]
        / CALCULATE (
            SUM ( tbl_fte[fte] ),
            FILTER ( tbl_fte, [personal_nr] = EARLIER ( tbl_fte[personal_nr] ) )
        )
RETURN
    costs* ftePercent 

For new date categories, please share some dummy data with a similar data structure with expected results to help us clarify your scenario.

Regards,

Xiaoxin Sheng

[GeorgW]

@Anonymous 

Thank you very much. But the result is not correct 😕

Result Power Bi:

Result should:

tbl_fte with month and year:

tbl_salary_costs with month and year:

Here the link to the dummy data sheet and the power bi file:

• xlsx with dummy data 
• pbix with test data 

[Anonymous · Accepted solution]

HI @GeorgW,

I modify the formula but still can't get the expected result as you display, you can try it if it suitable for your requirement:

Salary_cost = 
VAR cost =
    CALCULATE (
        SUM ( tbl_salary_costs[salary_costs] ),
        FILTER (
            ALLSELECTED ( tbl_salary_costs ),
            [month] = EARLIER ( tbl_fte[month] )
                && [name] = EARLIER ( tbl_fte[name] )
                && [year] = EARLIER ( tbl_fte[year] )
        )
    )
VAR rate =
    tbl_fte[fte]
        / CALCULATE (
            SUM ( tbl_fte[fte] ),
            FILTER (
                tbl_fte,
                [month] = EARLIER ( tbl_fte[month] )
                    && [name] = EARLIER ( tbl_fte[name] )
                    && [year] = EARLIER ( tbl_fte[year] )
            )
        )
RETURN
    cost * rate

Regards,

Xiaoxin Sheng

[GeorgW]

Thanks a lot!

[amitchandak]

@GeorgW , Assuming both tables are join on person number

new column in tbl_fte

new column = related(tbl_salary_costs[salary])*tbl_fte[fte]

[GeorgW]

Sorry, I made a mistake earlier. Brad Pitt is of course employed in two different departments, marketing and sales. And only 0.5 FTE overall (and not 1.0 FTE!!!)

But with your solution I get wrong values:

[GeorgW]

And I forgot to mention something else that might complicate matters: I have to do this calculation monthly, so both tables have two additional columns each: "Year" and "Month".

The proportional distribution of salary costs must then be done month by month, taking into account the FTE (1 per employee or less (for part-time))