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 differe...
  • 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 ) )

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

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

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