Forum Discussion

A-Aron's avatar
A-Aron
Helper I
4 years ago
Solved

Measure or New Column?

Hey Pros, im new to BI so here's my dilemma,

I have a sharepoint connection with several excel sheets of the same type that i have combined to make a single query. Every different sheet is a different site used by a differnet amount of users that log their daily hours. I am being asked to create an efficiency percentage per site that takes the logged hours against the potential hours that could be worked. Each site logs multiple jobs per day so i need to figure out how to make a DAX measure that will consider the site in one column then take a specified number (static number of hours) divided by how many entries per day. If anyone can understand what im trying to say any help would be awesome. heres an example table if it helps. 

DateSiteHoursPotential hours
1/1Neosho2=if(site=neosho,14)/COUNT,date)
1/1Nesosho4 
1/2Brim5 
1/2Brim3 
  • tamerj1's avatar
    tamerj1
    4 years ago

    A-Aron 
    Add a small lookup (dimintion) table that contains two columns: Site and # of Employees. A relationship can then be created between the two tables via [Site] columns. In your visula silce by the Site column from the lookup table and the measure would be
    DIVIDE ( SELECTEDVALUE ( LookupTable[# of Employees] ), COUNTROWS ( FactTable ) )

4 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi A-Aron 

    my understanding that for Neosho the result would be 14/2 = 7. What about other sites? Do you have a list that contains the sites vs this constant number or it is the same number across all sites?

  • A-Aron's avatar
    A-Aron
    Helper I
    4 years ago

    Hello thank you for replying!! So the number is different for each site becasue each site has diferent number of employees. So for neosho its 14 becasue they have 2. theres Brimfield which as 2, Newville has 3, Victorville has 2 Fontana has one and Gastonia has 2. I was just going to create a macro in the excel that just logs a 14 or whichever then append it to the query but i was hoping there is a cleaner way. 

    • tamerj1's avatar
      tamerj1
      Community Champion
      4 years ago

      A-Aron 
      Add a small lookup (dimintion) table that contains two columns: Site and # of Employees. A relationship can then be created between the two tables via [Site] columns. In your visula silce by the Site column from the lookup table and the measure would be
      DIVIDE ( SELECTEDVALUE ( LookupTable[# of Employees] ), COUNTROWS ( FactTable ) )