Calculate measure in two tables Related through Third

[inetguru]

I need to calculate the size of the possible premium, calculated in the context of personnel numbers based on several bonus schemes described for different levels of KPI execution.
In the first table of 'ШР' I have the Personnel number, position and salary amount.
The second table of the 'Шкалы' (Scale) describes bonus schemes. It contains the following values:
Position, Variant of calculation, Bonus rate.
In each of the tables, the post may occur several times. They are linked through a table of Posts containing only unique posts.
There is also a table 'ИсполнениеKPI' (Execution of KPI) with KPI execution level options.
I can not make a measure that will calculate the size of the premium with the detailing to the Calculation Option (without detailing to the post). It turns out only when you display in the details of posts from the table with unique values.
I tried such an option. Sum: = sumx (RELATEDTABLE ('Должности'); sum ('ШР' [salary]) * sum ('Шкалы' [rate of motivation]) * sum ('ИсполнениеKPI'' [KPI execution])), but it does not work.
Help please with the decision of this task.

https://yadi.sk/i/zfTuxbQ83H2MoW here is my file.

[Anonymous]

Hi @inetguru,

>>I tried such an option. Sum: = sumx (RELATEDTABLE ('Должности'); sum ('ШР' [salary]) * sum ('Шкалы' [rate of motivation]) * sum ('ИсполнениеKPI'' [KPI execution])), but it does not work.

The formula looks well, but I can't find any relationship between ИсполнениеKPI and other tables, your formula will calculate with total value of ИсполнениеKPI(17.1) and get the wrong result.

Can you share some detail content of this issue?

Regards,

Xiaoxin Sheng

[inetguru]

Hi, v-shex-msft

I have 3 options for the size of the bonus rate for each post.
For each of the bonus rate options, I need to calculate the size of the possible premium for each of the dimensions of the KPI implementation level (specified in the table ИсполнениеKPI ). The formula for calculating a simple = Salary * Bonus rate * Level of execution of the KPI.
The values ​​of the table ИсполнениеKPI  I display in the column headers. So I'm counting on a premium for various KPI implementations.
In the end, I need to calculate the total premium for each of the calculation options (out of 3) and for each of the KPI implementation levels (50% to 180% - ИсполнениеKPI ).
If I remove the name of the post or collapse the value of the calculation option, the formula does not give the correct result.

[Anonymous]

Hi @inetguru,

I reproduce the pivot table in power bi matrix visual, perhaps you can try it.

Regards,

Xiaoxin Sheng

[inetguru]

Thanks for the help, @Anonymous. However, I want to get another result. In my condition for one position in one calculation option, only one size of the motivation rate is provided. For example, for the position of Analyst (Аналитик) in the option of calculating 1 (вар.расч1), the motivation rate (ставка мотивации) is 35%, in the variant of calculation 2 (вар.расч2) the motivation rate (ставка мотивации) is 50%, in the variant of calculation 3  (вар.расч3) the motivation rate (ставка мотивации) is 15%.

These conditions are specified in the Scale table (Шкалы).

[Anonymous · Accepted solution]

Hi @inetguru,

>>For example, for the position of Analyst (Аналитик) in the option of calculating 1 (вар.расч1), the motivation rate (ставка мотивации) is 35%, in the variant of calculation 2 (вар.расч2) the motivation rate (ставка мотивации) is 50%, in the variant of calculation 3  (вар.расч3) the motivation rate (ставка мотивации) is 15%.

I modify the sample file, maybe you can try it:

Regards,
Xiaoxin Sheng

[inetguru]

Thanks a lot, @Anonymous.