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 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?

 

 

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

4 Replies

  • v-haibl-msft's avatar
    v-haibl-msft
    Microsoft 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

  • Jugu22's avatar
    Jugu22
    Regular Visitor
    8 years ago

    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's avatar
      v-haibl-msft
      Microsoft Employee
      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

      • Jugu22's avatar
        Jugu22
        Regular Visitor
        8 years ago

        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.