Forum Discussion

smather's avatar
smather
Helper III
6 years ago
Solved

Divide Measure by a Column

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
Need Help
  • smather's avatar
    smather
    6 years ago

    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. 

3 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    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's avatar
      smather
      Helper III
      6 years ago

      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's avatar
        smather
        Helper III
        6 years ago

        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.