Summarize values and multiply a value with a percentage

[Friedrich90123]

Hello,
I have two tables related with key table. One the one side there are the Salary and on the other side there are the monthly hours.

The data modell looks as follows:
Data Modell

I want to create a measure, that calculate the indirect payment and distribute it with the department or the month percentage to the cost units.
Here is an example:

I still working on the measure:

var table_calc_payment_direct = SUMMARIZE('tab_Navision';'Fact_Table'[key];'Fact_Table'[Year];'Fact_Table'[Month];'Fact_Table'[Department];'tab_Navision'[key];'tab_Navision'[Year];'tab_Navision'[Month];'tab_Navision'[Cost Unit Number];"Payment indirect";

var helper_payment_complete = CALCULATE(SUM('tab_DATEV'[Amount]);NATURALINNERJOIN('tab_Navision';NATURALINNERJOIN('Fact_Table';'tab_DATEV')))

var helper_sum_direct_hours  = CALCULATE(SUM('tab_Navision'[Hours]); FILTER(NATURALINNERJOIN('Fact_Table';'tab_Navision');'tab_Navision'[direct/ indirect]="direct"))

var helper_sum_direct_hours_ALL  = CALCULATE(SUM('tab_Navision'[Hours]);'tab_Navision'[direct/ indirect]="direct";ALLEXCEPT('tab_Navision';'tab_Navision'[key];'tab_Navision'[Year];'tab_Navision'[Month];'tab_Navision'[Reference Number]))

var helper_sum_hours_ALL  = CALCULATE(SUM('tab_Navision'[Hours]);ALLEXCEPT('tab_Navision';'tab_Navision'[key];'tab_Navision'[Year];'tab_Navision'[Month];'tab_Navision'[Reference Number]))

var helper_percentage_direct_hours = CALCULATE(DIVIDE(helper_sum_direct_hours;helper_sum_hours_ALL;BLANK()))

var helper_payment_direct = CALCULATE(helper_payment_complete  * helper_percentage_direct_hours)

// var helper_payment_indirect =  here should the difference between the payment - and the direct costs for the person for all cost unit calculated

// var helper_payment_direct_department = here should the direct payment of the whole department, filtered for the cost unit calculated.

// var helper_payment_direct_department_cost_unit = here should the direct payment of the whole department and cost units calculated..

// var helper_percentage_department = divide helper_payment_direct_department / helper_payment_direct_department_cost_unit 

// var helper_payment_direct_month = here should the direct payment of the whole month, filtered for the cost unit calculated.

// var helper_payment_direct_month_cost_unit = here should the direct payment of the whole month and cost units calculated.

// var helper_percentage_month = divide helper_payment_direct_month / helper_payment_direct_month_cost_unit

// var result_indirect_costs = multiply the indirect hours for all cost units with * helper_percentage_department

// var result_indirect_cost_HR_or_no_direct_hours/costs = multiply the indirect hours for all cost units with *  helper_percentage_month

return 
helper_payment_direct)
return
SUMX( table_calc_payment_direct;[Payment indirect])

Every help will be appreciated. I can not make any progress

The files can be found under:
Link to Google Drive 

[Friedrich90123]

Hello, the download link should work now:
https://drive.google.com/file/d/1PLXPI-8P8sT7a0nqm3ExTDPizzDcEbT7/view?usp=sharing 

Kind regards

[lbendlin]

Wo ist die [Direct/Indirect]  column in tab_navision? Und wo ist die Fact Tabelle?

[Friedrich90123]

Hello Ibendlin,

the column [Direct/Indirect] is calculated in the power query:
= Table.AddColumn(#"Filtered Rows", "direct/ indirect", each if [Cost Unit] = "Default" then "indirect" else "direct")
Every cost unit is direct, except default. This cost unit is indirect.

The Fact Table will be created in the power query with the key from the Datev and the Navision table. The key is a combination from employee ID.Year.Month; The Idea for the fact Table is to join the Datev and Navision Table with 1:n relation.

Kind regards

[lbendlin]

Link asks for access