Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
I 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
Solved! Go to Solution.
HI @don_writer
Because the number is repeated over and over, it suggests there is a relationship missing between the two tables.
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] )
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])
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.
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.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |