Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
elliotdixon
Responsive Resident
Responsive Resident

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

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.

View solution in original post

5 REPLIES 5
leonardmurphy
Skilled Sharer
Skilled Sharer

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 Madison Power BI User Group.
Bjoern
Continued Contributor
Continued Contributor

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

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

---
In Wisconsin? Join the Madison Power BI User Group.

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.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.