Divide one column by another

[elliotdixon]

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

[leonardmurphy]

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

[Bjoern]

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

[leonardmurphy]

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

[elliotdixon · Accepted solution]

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]

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