Create relation in 2 tables

[Jugu22]

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 lot of values which are on the same date on each table. What I want is to make an histogram chart with the numerator and the denominateur with the same dates.

Is it clear?

[Jugu22]

Hello,

Firstly, thank you for your answer.

The problem is I can't sum the amount of sales because I need to put filter on some colomns to have the right amount to put on the numerator, same for the denominator.

If I sum my amount per date, I loose every filters so I can't create the ratio.

Is there another solution except group by?

Thank you very much,

Julien

[v-haibl-msft · Accepted solution]

@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

[Jugu22]

It works on my file, thanks a lot.

I have a last question, here is my formula :

 RETURN
   (CALCULATE(CALCULATE(SUM('Table1'[Actual Sales]);OR('Table1[Filter]=1;'Table1'[Filter1]=2));'Table1'[Filter2]="00164"))/TempExpectedSales

But I want Filter2 (in red) not to be fixed. Can I use a code to change this filter from the rapport? It would be perfect if I could use a segment to change the filter of the measure directly to make the changes dynamic.  

I don't know if I'm clear, again thanks a lot for your solution.

[v-haibl-msft]

@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