Dynamic Calculation based on Attribute across relationships

[glykk]

HI,

I'm having an issue with some caluclations I need to perform.

My first table looks something like this -
Data
Name | unit | amount

John   | B     | 5

Paul    | A    | 10

fooo    | B    | 5

baar    | A    | 2

...

I then have a table of assumed ratios:

Assumptions

Unit  | Cost of Sales | support cost
A      | 0.16%           | 0.14%  

B       | 0.18 %          | 0.16 %

....

Now I need to calculate certain measures depending on what the Unit assigned is so for example I would calculate the Cost of Sales ratio as 

A cost of sales =  CALCULATE(SUM('Data'[Amount]*SUM'Assumptions'[Cost of Sales], filter(Assumptions,Assumptions[Unit]="A")) 

B cost of sales = CALCULATE(SUM('Data'[Amount]*SUM'Assumptions'[Cost of Sales], filter(Assumptions,Assumptions[Unit]="B"))

C cost of sales = CALCULATE(SUM('Data'[Amount]*SUM'Assumptions'[Cost of Sales], filter(Assumptions,Assumptions[Unit]="C"))

....

N cost of sales = CALCULATE(SUM('Data'[Amount]*SUM'Assumptions'[Cost of Sales], filter(Assumptions,Assumptions[Unit]="N"))

Total cost of sales = [B cost of sales] + [A cost of sales] +... [N cost of sales]

this approach is far too tedious when you are working with 50+ units across and multiple assumptions -

if there a better way of performing calculations like this?

[LivioLanzo · Accepted solution]

Hi @glykk,

Once you have created a One-To-Many relationship, you can do it like this:

=
SUMX (
    VALUES ( Assumptions[Cost of Sales] ),
    Assumptions[Cost of Sales] * CALCULATE ( SUM ( Data[amount] ) )
)