Forum Discussion

Jugu22's avatar
Jugu22
Regular Visitor
8 years ago
Solved

Create relation in 2 tables

Hello,   I want to create a relation between 2 tables to create a ratio : on the numerator an amount from a table and on the denominator an amount on the other table.   The problem is I have a lo...
Data Modeling
  • v-haibl-msft's avatar
    v-haibl-msft
    8 years ago

    Jugu22

     

    You can create a measure with following DAX formula without creating relationship between these two tables.

     

    Measure = 
VAR TempDate =
    CALCULATE ( MAX ( Table1[Date] ) )
VAR TempExpectedSales =
    CALCULATE (
        SUM ( Table2[Expected Sales] ),
        FILTER ( ALL ( Table2 ), Table2[Date] = TempDate )
    )
RETURN
    CALCULATE ( SUM ( Table1[Actual Sales] ) ) / TempExpectedSales

     

    Best Regards,
    Herbert

v-haibl-msft's avatar
v-haibl-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Jugu22

 

I'd like to give a simple sample as below (hope I understand your scenario exactly). We have two tables like below.

 

We can first summarize both of tables using the group function in Query Editor.

 

Now we can create relationship between these two tables.

 

To get the ratio, we just need to create a calculated column with following formula.

 

ratio_Column = Table1[Actual Sales] / RELATED( Table2[Expected Sales] )

 

 

Best Regards,
Herbert