cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
elliotdixon Member
Member

Divide one column by another

I have two tables, JobRuns & JobRunDocket

 

I need to divide the number runs in one table by the number of associated dockets in another table. Each run can have multiple dockets.

 

Number of loads is what I need to know and generally this is done by

Calculate(Count(JobRuns[Run_ID]))

however we have started having two dockets for some runs.

 

Hopefully the new calculation will give me an accurate total for number of loads. Will cause a load to be 0.5 if there are two dockets or 0.333 if three etc.

 

JobRunDocket[DOC_RUN_ID) is related to JobRuns[Run_ID)

There is a column that states the number of dockets associated to the Run = JobRunDocket[Doc_ID].

 

Would like to have the calculation on the JobRuns[Run_ID] column as that table has other important columns required for calculations

 

Cheers in advance for any help with this one.

ED

1 ACCEPTED SOLUTION

Accepted Solutions
elliotdixon Member
Member

Re: Divide one column by another

Cheers for all the help people.

Unfortunately couldnt manage to get the answer I wanted with the calculations recommended.

Did however manage to connect to an SQL view that was written with the right data in it. 

Didn't know that was possible but all working well now.

ED.

5 REPLIES 5
leonardmurphy Established Member
Established Member

Re: Divide one column by another

Assuming you have a measure:

 

NumberOfLoads = CALCULATE(COUNT(JobRuns[RunID]))

Then try this as a measure:

 

Number Of Loads Divided By Docket = SUMX(JobRuns, [NumberOfLoads] / COUNTROWS(RELATEDTABLE(JobRunDocket)))

The SUMX in this case is because, if you are looking at total number of runs for a day, you want to look at each individual run and divide it by the corresponding number of dockets. You don't want to count the total number of runs and divide it by the total number of corresponding dockets.

 

If you're trying to do a column rather than a measure, then you don't need the SUMX part:

 

Number Of Loads Divided By Docket Column = [NumberOfLoads] / COUNTROWS(RELATEDTABLE(JobRunDocket))

 

---
In Wisconsin? Join the Microsoft BI Professionals - Wisconsin group.
Bjoern Established Member
Established Member

Re: Divide one column by another

I would recommend using the divide()-formula instead of "/", as it has error-handling included.

leonardmurphy Established Member
Established Member

Re: Divide one column by another

Excellent point. I always forget that until I get a divide by zero error. 

---
In Wisconsin? Join the Microsoft BI Professionals - Wisconsin group.
elliotdixon Member
Member

Re: Divide one column by another

Cheers for all the help people.

Unfortunately couldnt manage to get the answer I wanted with the calculations recommended.

Did however manage to connect to an SQL view that was written with the right data in it. 

Didn't know that was possible but all working well now.

ED.

Amir2550 Frequent Visitor
Frequent Visitor

Re: Divide one column by another

newcolName =
DIVIDE(
SUM('table1'[col1]),
SUM('table1'[col2])
)