topic Re: Measure or New Column? in DAX Commands and Tips

Measure or New Column?

A-Aron — Fri, 29 Jul 2022 11:23:10 GMT

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.

Date	Site	Hours	Potential hours
1/1	Neosho	2	=if(site=neosho,14)/COUNT,date)
1/1	Nesosho	4
1/2	Brim	5
1/2	Brim	3

Re: Measure or New Column?

tamerj1 — Fri, 29 Jul 2022 11:52:01 GMT

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?

Re: Measure or New Column?

A-Aron — Fri, 29 Jul 2022 12:01:01 GMT

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.

Re: Measure or New Column?

tamerj1 — Fri, 29 Jul 2022 12:23:37 GMT

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

Re: Measure or New Column?

A-Aron — Fri, 29 Jul 2022 12:51:39 GMT

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