Forum Discussion

glykk's avatar
glykk
Regular Visitor
7 years ago
Solved

Dynamic Calculation based on Attribute across relationships

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?

  • 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] ) )
    )

     

1 Reply

  • 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] ) )
    )