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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.