cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

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?

 

 

1 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

Create relation in 2 tables_6.jpg

 

Best Regards,
Herbert

View solution in original post

4 REPLIES 4
Regular Visitor

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

 

@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

Create relation in 2 tables_6.jpg

 

Best Regards,
Herbert

View solution in original post

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.

Microsoft
Microsoft

@Jugu22

 

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

Create relation in 2 tables_1.jpg

 

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

Create relation in 2 tables_2.jpg

Create relation in 2 tables_3.jpg

 

Now we can create relationship between these two tables.

Create relation in 2 tables_3.jpg

 

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

 

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

Create relation in 2 tables_4.jpg

 

Create relation in 2 tables_5.jpg

 

Best Regards,
Herbert

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors