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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
don_writer
Helper II
Helper II

Summarizing values from two tables by two columns

Hi kind and gentle experts,

 

I have two tables.

Active table has six columns = EEID, ActiveDate, ActiveYear, ActiveMonth, Company, Headcount (always 1)

TermTable has six columns = EEID, TermDate,  TermYear, TermMonth, Company, Termcount (always 1)

 

I'd like to build a table that shows

Year, Month, Company, sum(Term[Termcount])/sum(Active[Headcount] - seen as a percentage

 

 

code_error.JPGI get an error saying the month cannot be determined and I suspect it can't use two groupBy columns.

 

How should I go about doing this? Seems like it should be relatively simple but has been evading me. 

 

Best regards,

~Don

1 ACCEPTED SOLUTION

HI @don_writer

 

Because the number is repeated over and over, it suggests there is a relationship missing between the two tables.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

6 REPLIES 6
Phil_Seamark
Employee
Employee

Hi @don_writer

 

Looks like you are trying to create an aggregate measure over the column when I think you are simply trying to group (and rename) the column.

 

Please try

 

Turnover = 
SELECTCOLUMNS(
   SUMMARIZE (
    Term,
    Term[company_name] ,
    Term[Month] ,
    "MonthlyTerms" , SUM(Term[termaccount]) ,
    "Monthly Headcount",sum(Active[headcount])
   )
, "Term" ,[Term]
, "Company Name", [company_name] 
, "RptMonth" , [Month] 
, "Monthly Terms",[MonthlyTerms]
, "Monthly Headcount" , [Monthly Headcount]
)

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Thank you for your reponse,

 

I made some adjustment and I am getting a "scaler error."

 

Here is what I have thus far.  What am I missing?

 

 

Turnover = 
SELECTCOLUMNS(
   SUMMARIZE (
    Term,
    Term[Year],
    Term[company_name] ,
    Term[Month] ,
    "Monthly Terms" , SUM(Term[termcount]) ,
    "Monthly Headcount",sum(Active[headcount])
   ),

"Company Name", Term[company_name], 
"RptYear", Term[Year],
"RptMonth" , Term[Month], 
"Monthly Terms",[Monthly Terms], 
"Monthly Headcount" , [Monthly Headcount]
)
       

 

 

BTW the next step is to divide Monthly Terms by Monthly Headcount and display it as a percentage.

Hi @don_writer

 

Do you get the error if you just run it as a SUMMARIZE only?  And are you creating this as a calculated table (and not a calculated measure)?

 

eg.

 

turnover =  SUMMARIZE (
    Term,
    Term[Year],
    Term[company_name] ,
    Term[Month] ,
    "Monthly Terms" , SUM(Term[termcount]) ,
    "Monthly Headcount",sum(Active[headcount])

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Summarize marginally worked 😄  However it didn't sum the Headcount across months. Instead every row displays the total headcount in the entire dataset.

 

Year   Month   Company    Terms  Headcount

2012  January Company A    9        390000

2012  January Company B    3        390000

2012  January Company C    5        390000

2013  January Company A    8        390000

2013  January Company B    3        390000

2013  January Company C    4        390000

HI @don_writer

 

Because the number is repeated over and over, it suggests there is a relationship missing between the two tables.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Here are my relationships:

 

I have a Date table with Date, Month, Year, and YearMonth

I have an Employee_List table with distinct values from both Active and Termination tables. I've validated that there are no duplicates.

I have a Company_ref table that has distinct companies listed by company_code

 

The Active[ActiveDate] and Terms[Termination_date] are linked to Date[Date] (Active[ActiveDate] and Date[Date] are bidirectional relationships)

Active[Company_Code], Terms[Company_Code] are linked to  Company_ref[Company_Code]

Active[EE_ID] and Terms[EE_ID] and linked to Employee_list. Active to EE_ID are bidirectional.

 

Here is my new code...

Turnover = 
SUMMARIZECOLUMNS(
    'Date'[Calendar YearMonthNumber],
    'Date'[Calendar Year],
    'Date'[Calendar Month],
    Terms[employee_subgroup],
    ref_company_code[Company Text],
    Terms[personnel_area_name], 
              FILTER(
                Active,
                Active[employee_subgroup]<>"Expatriate"&&
                Active[employee_subgroup]<>"External Staff"&&
                Active[employee_subgroup]<>"Intern"&&
                FIND("LOA Unpaid",Active[employee_subgroup],,0)=0&&
                Active[employee_subgroup]<>"Offshore Contractor"&&
                Active[employee_subgroup]<>"Foreign Officers"&&
                Active[employee_subgroup]<>"On-Site Vendor"),
                
    "Monthly Terms", sum(Terms[termcount]),
    "Monthly Headcount", sum(Active[headcount]),
    "Monthly Turnover",(sum(Terms[termcount])/sum(Active[headcount]))*100
)

 

What am I doing wrong?

 

Thank you.

 

 

 

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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