cancel
Showing results for
Search instead for
Did you mean:
Frequent Visitor

## Divide two columns from different tables

Hello everyone, currently I have two tables: table 1 and table 2 in Power BI

Table 1:

 Date Column A 01/01 120 02/01 34 03/01 90 04/01 109

Table 2:

 Date Column B 01/01 200 02/01 103 03/01 270 04/01 300

These tables are made in Power BI after I dragged two fields for each one.

For table 1, they are the "Date" column and column A (which is the count of "True" values for each day).

 Date Column C 01/01 True 01/01 False 01/01 True 02/01 True 03/01 True 03/01 True 04/01 True 04/01 False 04/01 False

Table 2 is made by counting the number of clicks for each day

What I want to do is: Column A divided by Column B (A/B for short) for each day and I will use that result to draw a graph. What can I do to achieve this?

Thanks for your help!

1 ACCEPTED SOLUTION
Super User

you can create a date table and create relationships with table 1 and table 2

Then create a measure

``````Measure =
VAR _1=CALCULATE(COUNTROWS(Table1),FILTER(Table1,Table1[ColumnC]=TRUE()))
VAR _2=sum(Table2[click])
return _1/_2``````

pls see the attachment below

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

8 REPLIES 8
Helper III

Hi @dididing2001 ,

You can use measure

Measure = DIVIDE(SUM('Table A'[Column A]),SUM('Table B'[Column B]))
Super User

you can create a date table and create relationships with table 1 and table 2

Then create a measure

``````Measure =
VAR _1=CALCULATE(COUNTROWS(Table1),FILTER(Table1,Table1[ColumnC]=TRUE()))
VAR _2=sum(Table2[click])
return _1/_2``````

pls see the attachment below

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Frequent Visitor

@ryan_mayu Thanks a lot for your help! Your method works like a charm for me!

Super User

you are welcome

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Frequent Visitor

@ryan_mayu Unfortunately, after checking the result, I'm afraid that the division didn't work well. I attached an image and my file below for your reference.

The conversion rate on 01/11/2019 is 0.7, which is 412/590. Meanwhile, what I want is the result of 412/2198. I cannot figure out the problem in this calculation.

Super User

you need to imporve the data model, since the relationship between date table and two fact tables are inactive. I have deleted all the rest relationships. you can re-create the relationship. However, I still think you can simplify the data model.

pls see the attachment below.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Frequent Visitor

The conversion rate works now. Your advice helps me a lot when it comes to data modelling. Thanks a lot!

Super User

you are welcome

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

## Helpful resources

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

#### Check it out!

Click here to read more about the May 2022 updates!

Top Solution Authors
Top Kudoed Authors