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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
A-Aron
Helper I
Helper I

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 
1 ACCEPTED SOLUTION

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

View solution in original post

4 REPLIES 4
A-Aron
Helper I
Helper I

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. 

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

This is gold thank you !!!!!!!

tamerj1
Super User
Super User

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?

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors