Divide Measure by a Column

[smather]

Hi

I'm trying to divide a measure by a column (there's an active relationship between the tables) but it's not giving me the expected answer. 

The measure works out the number of working days someone has had in a month, and the formula is:

Working Days = CALCULATE(DISTINCTCOUNT(Timesheets[local_date]),Timesheets[Ut] ="1") -- the Ut Column is a formula that makes sure two points are hit to class it as a working day. 

I then have a table that has the amount of working days per month for the year. 

So currently, for Jan, we have one person who Working Days measure works out as 21 for Jan. The number of working days in Jan is 22. So 21 / 22 = 0.9545454 * 100 gives me 95.45% for utilization for that month. 

But if I do a measure of: 

DIVIDE = [Working Days] / SUM('Number of Work Days Per Month'[Work Days]) and change it to % I get 8.30% 

I'm missing something very obvious here, but I'm too thick to figure out what. Any help would be fab, thanks

[parry2k]

@smather can you share sample data and relationship diagram? Also how you are visualizing the data? Create another measure for sum of working days to check what value you are getting.

[smather]

Hi @parry2k 

Currently it's in a graph, so it can be filtered by month and also to give a YTD view too. Here's how things look -- I dunno how to share, so hopefully screen grabs will be sufficient. 

The Ut col is a forumula - Ut = IF(AND(Timesheets[Workday] = "Yes", Timesheets[Utilization] = "Install & Service"), "1","")

Both workday and utilization are forumulas too: 

Workday = IF(Timesheets[jobcode_1] = "Bank Holiday", "No",IF(

Timesheets[local_day] = "Sat", "No", IF(
Timesheets[local_day] = "Sun", "No", "Yes")))

Utilization = IF(Timesheets[jobcode_1] = "1. Involve Tasks", "Non-Install",IF(

Timesheets[jobcode_1] = "2. Service Calls", "Install & Service", IF(

Timesheets[jobcode_1] = "Annual Leave", "Non-Install", IF(

Timesheets[jobcode_1] = "Bank Holiday", "Non-Install", IF(

Timesheets[jobcode_1] = "Sick", "Non-Install", IF(

Timesheets[jobcode_1] = "Compassionate Leave", "Non-Install", IF(

Timesheets[jobcode_1] = "Unpaid Leave", "Non-Install","Install & Service")))))))

The number of work days per month is just a table: 

The relationship is done on month. 

Hopefully some of that helps!

[smather · Accepted solution]

I was missing something! My realationship was wrong. As soon as it set it to both ways, rather than single, everything worked as it should.