topic Re: Help with calculating voluntary turnover in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Help-with-calculating-voluntary-turnover/m-p/3223257#M117934 <P>I feel like we are getting close but this VAR fields are coming up with errors. See screenshot belwo.</P><P><img /></P><P>&nbsp;</P> Sat, 06 May 2023 15:10:05 GMT Datachef 2023-05-06T15:10:05Z Help with calculating voluntary turnover https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Help-with-calculating-voluntary-turnover/m-p/3210290#M117066 <P>Hi there,&nbsp;</P><P>&nbsp;</P><P>I have all 2022 staff turnover data isolated in one excel document. Our Voluntary turnover rate needs to consider 3 conditions: was the employee permanent? was the employee full-time? did the employee leave voluntarily? Each of these pieces of information are in separate columns in my data. I just need to add all employees who were terminated if they meet these 3 conditions. What dax formula should I use?</P> Fri, 28 Apr 2023 01:31:59 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Help-with-calculating-voluntary-turnover/m-p/3210290#M117066 Datachef 2023-04-28T01:31:59Z Re: Help with calculating voluntary turnover https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Help-with-calculating-voluntary-turnover/m-p/3210868#M117097 <P>can you please show some sample data</P> Fri, 28 Apr 2023 09:03:40 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Help-with-calculating-voluntary-turnover/m-p/3210868#M117097 eliasayyy 2023-04-28T09:03:40Z Re: Help with calculating voluntary turnover https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Help-with-calculating-voluntary-turnover/m-p/3211905#M117164 <P>Hey,</P><P>&nbsp;</P><P>I hope you are doing well!</P><P>&nbsp;</P><P>Share some demo data along with the desire output.</P><P>&nbsp;</P><P>thanks,</P><P>Ajendra</P> Fri, 28 Apr 2023 20:49:56 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Help-with-calculating-voluntary-turnover/m-p/3211905#M117164 Ajendra 2023-04-28T20:49:56Z Re: Help with calculating voluntary turnover https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Help-with-calculating-voluntary-turnover/m-p/3217934#M117531 <P>Sure, here you go</P><TABLE><TBODY><TR><TD>Name of Action Type</TD><TD>P.Subarea Text</TD><TD>Personnel Area Text</TD><TD>Name of Employee Group</TD><TD>Name of EE Subgroup</TD><TD>From</TD><TD>End Date</TD><TD>Company Name</TD><TD>Start Date</TD><TD>Age</TD><TD>Type</TD><TD>YOS</TD><TD>Flag</TD></TR><TR><TD>Termination</TD><TD>CoB CUPE</TD><TD>COB - CUPE</TD><TD>Permanent</TD><TD>Full-Time</TD><TD>09/01/2022</TD><TD>12/31/9999</TD><TD>City of Barrie</TD><TD>9/28/1987</TD><TD>67</TD><TD>Retirement</TD><TD>35.25833</TD><TD>Permanent Full-Time</TD></TR><TR><TD>Retirement with Benefits</TD><TD>CoB NUG</TD><TD>COB - Non-Union</TD><TD>Retired</TD><TD>Full-Time</TD><TD>06/01/2022</TD><TD>12/31/9999</TD><TD>City of Barrie</TD><TD>6/13/1988</TD><TD>60</TD><TD>Retirement</TD><TD>34.55</TD><TD>Retired Full-Time</TD></TR><TR><TD>Retirement with Benefits</TD><TD>CoB NUG</TD><TD>COB - Non-Union</TD><TD>Retired</TD><TD>Full-Time</TD><TD>05/28/2022</TD><TD>12/31/9999</TD><TD>City of Barrie</TD><TD>8/2/1988</TD><TD>58</TD><TD>Retirement</TD><TD>34.41389</TD><TD>Retired Full-Time</TD></TR><TR><TD>Retirement with Benefits</TD><TD>CoB NUG</TD><TD>COB - Non-Union</TD><TD>Retired</TD><TD>Full-Time</TD><TD>06/26/2022</TD><TD>12/31/9999</TD><TD>City of Barrie</TD><TD>6/25/1990</TD><TD>61</TD><TD>Retirement</TD><TD>32.51667</TD><TD>Retired Full-Time</TD></TR><TR><TD>Retirement with Benefits</TD><TD>CoB CUPE</TD><TD>COB - CUPE</TD><TD>Retired</TD><TD>Full-Time</TD><TD>02/26/2022</TD><TD>12/31/9999</TD><TD>City of Barrie</TD><TD>6/24/2020</TD><TD>61</TD><TD>Retirement</TD><TD>2.519444</TD><TD>Retired Full-Time</TD></TR><TR><TD>Retirement with Benefits</TD><TD>CoB CUPE</TD><TD>COB - CUPE</TD><TD>Retired</TD><TD>Full-Time</TD><TD>01/01/2022</TD><TD>12/31/9999</TD><TD>City of Barrie</TD><TD>10/17/1994</TD><TD>59</TD><TD>Retirement</TD><TD>28.20556</TD><TD>Retired Full-Time</TD></TR><TR><TD>Retirement with Benefits</TD><TD>FF BPFFA</TD><TD>COB - BPFFA/Comm Op</TD><TD>Retired</TD><TD>Full-Time</TD><TD>11/22/2022</TD><TD>12/31/9999</TD><TD>City of Barrie</TD><TD>10/28/1996</TD><TD>59</TD><TD>Retirement</TD><TD>26.175</TD><TD>Retired Full-Time</TD></TR><TR><TD>Termination</TD><TD>CoB CUPE</TD><TD>COB - CUPE</TD><TD>Permanent</TD><TD>Full-Time</TD><TD>08/01/2022</TD><TD>12/31/9999</TD><TD>City of Barrie</TD><TD>4/21/1997</TD><TD>60</TD><TD>Retirement</TD><TD>25.69444</TD><TD>Permanent Full-Time</TD></TR><TR><TD>Termination</TD><TD>CoB CUPE</TD><TD>COB - CUPE</TD><TD>Casual</TD><TD>Full-Time</TD><TD>11/05/2022</TD><TD>12/31/9999</TD><TD>City of Barrie</TD><TD>4/12/2021</TD><TD>57</TD><TD>End of Contract</TD><TD>1.719444</TD><TD>Casual Full-Time</TD></TR><TR><TD>Termination</TD><TD>CoB CUPE</TD><TD>COB - CUPE</TD><TD>Permanent</TD><TD>Full-Time</TD><TD>08/01/2022</TD><TD>12/31/9999</TD><TD>City of Barrie</TD><TD>1/2/2001</TD><TD>65</TD><TD>Retirement</TD><TD>21.99722</TD><TD>Permanent Full-Time</TD></TR><TR><TD>Termination</TD><TD>CoB NUG</TD><TD>COB - Non-Union</TD><TD>Casual</TD><TD>Part-Time</TD><TD>01/01/2022</TD><TD>12/31/9999</TD><TD>City of Barrie</TD><TD>9/2/2020</TD><TD>67</TD><TD>End of Contract</TD><TD>2.330556</TD><TD>Casual Part-Time</TD></TR><TR><TD>Termination</TD><TD>CoB CUPE</TD><TD>COB - CUPE</TD><TD>Permanent</TD><TD>Full-Time</TD><TD>11/01/2022</TD><TD>12/31/9999</TD><TD>City of Barrie</TD><TD>10/17/2002</TD><TD>47</TD><TD>Voluntary</TD><TD>20.20556</TD><TD>Permanent Full-Time</TD></TR><TR><TD>Termination</TD><TD>CoB CUPE</TD><TD>COB - CUPE</TD><TD>Permanent</TD><TD>Full-Time</TD><TD>09/16/2022</TD><TD>12/31/9999</TD><TD>City of Barrie</TD><TD>10/25/2004</TD><TD>65</TD><TD>Retirement</TD><TD>18.18333</TD><TD>Permanent Full-Time</TD></TR><TR><TD>Termination</TD><TD>CoB CUPE</TD><TD>COB - CUPE</TD><TD>Permanent</TD><TD>Full-Time</TD><TD>07/23/2022</TD><TD>12/31/9999</TD><TD>City of Barrie</TD><TD>12/5/2005</TD><TD>38</TD><TD>Voluntary</TD><TD>17.07222</TD><TD>Permanent Full-Time</TD></TR><TR><TD>Termination</TD><TD>CoB NUG</TD><TD>COB - Non-Union</TD><TD>Casual</TD><TD>Full-Time</TD><TD>10/25/2022</TD><TD>12/31/9999</TD><TD>City of Barrie</TD><TD>10/19/2022</TD><TD>53</TD><TD>End of Contract</TD><TD>0.2</TD><TD>Casual Full-Time</TD></TR><TR><TD>Termination</TD><TD>CoB CUPE</TD><TD>COB - CUPE</TD><TD>Permanent</TD><TD>Full-Time</TD><TD>08/05/2022</TD><TD>12/31/9999</TD><TD>City of Barrie</TD><TD>7/23/2007</TD><TD>60</TD><TD>Retirement</TD><TD>15.43889</TD><TD>Permanent Full-Time</TD></TR><TR><TD>Termination</TD><TD>CoB NUG</TD><TD>COB - Non-Union</TD><TD>Casual</TD><TD>Part-Time</TD><TD>01/04/2022</TD><TD>12/31/9999</TD><TD>City of Barrie</TD><TD>9/3/2020</TD><TD>60</TD><TD>Voluntary</TD><TD>2.327778</TD><TD>Casual Part-Time</TD></TR><TR><TD>Termination</TD><TD>CoB NUG</TD><TD>COB - Non-Union</TD><TD>Casual</TD><TD>Part-Time</TD><TD>09/23/2022</TD><TD>12/31/9999</TD><TD>City of Barrie</TD><TD>5/25/2013</TD><TD>30</TD><TD>End of Contract</TD><TD>9.6</TD><TD>Casual Part-Time</TD></TR><TR><TD>Termination</TD><TD>CoB NUG</TD><TD>COB - Non-Union</TD><TD>Casual</TD><TD>Part-Time</TD><TD>09/23/2022</TD><TD>12/31/9999</TD><TD>City of Barrie</TD><TD>10/11/2007</TD><TD>30</TD><TD>End of Contract</TD><TD>15.22222</TD><TD>Casual Part-Time</TD></TR><TR><TD>Termination</TD><TD>CoB CUPE</TD><TD>COB - CUPE</TD><TD>Casual</TD><TD>Full-Time</TD><TD>11/19/2022</TD><TD>12/31/9999</TD><TD>City of Barrie</TD><TD>4/12/2021</TD><TD>67</TD><TD>End of Contract</TD><TD>1.719444</TD><TD>Casual Full-Time</TD></TR><TR><TD>Termination</TD><TD>CoB CUPE</TD><TD>COB - CUPE</TD><TD>Permanent</TD><TD>Full-Time</TD><TD>10/01/2022</TD><TD>12/31/9999</TD><TD>City of Barrie</TD><TD>8/15/2011</TD><TD>35</TD><TD>Voluntary</TD><TD>11.37778</TD><TD>Permanent Full-Time</TD></TR><TR><TD>Termination</TD><TD>CoB CUPE</TD><TD>COB - CUPE</TD><TD>Permanent</TD><TD>Full-Time</TD><TD>07/01/2022</TD><TD>12/31/9999</TD><TD>City of Barrie</TD><TD>12/15/2008</TD><TD>55</TD><TD>Retirement</TD><TD>14.04444</TD><TD>Permanent Full-Time</TD></TR><TR><TD>Termination</TD><TD>CoB CUPE</TD><TD>COB - CUPE</TD><TD>Permanent</TD><TD>Full-Time</TD><TD>04/28/2022</TD><TD>12/31/9999</TD><TD>City of Barrie</TD><TD>3/23/2009</TD><TD>47</TD><TD>Voluntary</TD><TD>13.77222</TD><TD>Permanent Full-Time</TD></TR><TR><TD>Termination</TD><TD>CoB NUG</TD><TD>COB - Non-Union</TD><TD>Casual</TD><TD>Part-Time</TD><TD>09/23/2022</TD><TD>12/31/9999</TD><TD>City of Barrie</TD><TD>9/6/2016</TD><TD>28</TD><TD>End of Contract</TD><TD>6.319444</TD><TD>Casual Part-Time</TD></TR><TR><TD>Salary Continuation</TD><TD>CoB NUG</TD><TD>COB - Non-Union</TD><TD>Permanent</TD><TD>Full-Time</TD><TD>11/21/2022</TD><TD>12/31/9999</TD><TD>City of Barrie</TD><TD>1/24/2011</TD><TD>40</TD><TD>Involuntary</TD><TD>11.93611</TD><TD>Permanent Full-Time</TD></TR><TR><TD>Termination</TD><TD>CoB CUPE</TD><TD>COB - CUPE</TD><TD>Permanent</TD><TD>Full-Time</TD><TD>06/10/2022</TD><TD>12/31/9999</TD><TD>City of Barrie</TD><TD>5/16/2011</TD><TD>36</TD><TD>Voluntary</TD><TD>11.625</TD><TD>Permanent Full-Time</TD></TR><TR><TD>Retirement with Benefits</TD><TD>FF BPFFA</TD><TD>COB - BPFFA/Comm Op</TD><TD>Retired</TD><TD>Full-Time</TD><TD>06/01/2022</TD><TD>12/31/9999</TD><TD>City of Barrie</TD><TD>4/7/2022</TD><TD>54</TD><TD>Retirement</TD><TD>0.733333</TD><TD>Retired Full-Time</TD></TR><TR><TD>Termination</TD><TD>CoB NUG</TD><TD>COB - Non-Union</TD><TD>Casual</TD><TD>Part-Time</TD><TD>02/08/2022</TD><TD>12/31/9999</TD><TD>City of Barrie</TD><TD>7/27/2020</TD><TD>44</TD><TD>End of Contract</TD><TD>2.427778</TD><TD>Casual Part-Time</TD></TR><TR><TD>Termination</TD><TD>CoB NUG</TD><TD>COB - Non-Union</TD><TD>Casual</TD><TD>Part-Time</TD><TD>01/23/2022</TD><TD>12/31/9999</TD><TD>City of Barrie</TD><TD>9/12/2020</TD><TD>27</TD><TD>Voluntary</TD><TD>2.302778</TD><TD>Casual Part-Time</TD></TR><TR><TD>Termination</TD><TD>CoB CUPE</TD><TD>COB - CUPE</TD><TD>Casual</TD><TD>Full-Time</TD><TD>11/17/2022</TD><TD>12/31/9999</TD><TD>City of Barrie</TD><TD>4/12/2021</TD><TD>62</TD><TD>End of Contract</TD><TD>1.719444</TD><TD>Casual Full-Time</TD></TR><TR><TD>Termination</TD><TD>CoB CUPE</TD><TD>COB - CUPE</TD><TD>Permanent</TD><TD>Full-Time</TD><TD>09/01/2022</TD><TD>12/31/9999</TD><TD>City of Barrie</TD><TD>11/17/2011</TD><TD>46</TD><TD>Voluntary</TD><TD>11.12222</TD><TD>Permanent Full-Time</TD></TR><TR><TD>Termination</TD><TD>CoB NUG</TD><TD>COB - Non-Union</TD><TD>Casual</TD><TD>Part-Time</TD><TD>09/23/2022</TD><TD>12/31/9999</TD><TD>City of Barrie</TD><TD>12/13/2016</TD><TD>27</TD><TD>End of Contract</TD><TD>6.05</TD><TD>Casual Part-Time</TD></TR><TR><TD>Termination</TD><TD>CoB NUG</TD><TD>COB - Non-Union</TD><TD>Permanent</TD><TD>Full-Time</TD><TD>09/24/2022</TD><TD>12/31/9999</TD><TD>City of Barrie</TD><TD>7/3/2012</TD><TD>41</TD><TD>Voluntary</TD><TD>10.49444</TD><TD>Permanent Full-Time</TD></TR></TBODY></TABLE> Wed, 03 May 2023 13:30:00 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Help-with-calculating-voluntary-turnover/m-p/3217934#M117531 Datachef 2023-05-03T13:30:00Z Re: Help with calculating voluntary turnover https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Help-with-calculating-voluntary-turnover/m-p/3218568#M117571 <P><img /></P><P>Check this one...Thanks!!</P> Wed, 03 May 2023 19:21:54 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Help-with-calculating-voluntary-turnover/m-p/3218568#M117571 Ajendra 2023-05-03T19:21:54Z Re: Help with calculating voluntary turnover https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Help-with-calculating-voluntary-turnover/m-p/3222814#M117879 <P>Thanks for your help!! Power BI isn't recognizing the term FactTable&nbsp;</P> Fri, 05 May 2023 20:06:21 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Help-with-calculating-voluntary-turnover/m-p/3222814#M117879 Datachef 2023-05-05T20:06:21Z Re: Help with calculating voluntary turnover https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Help-with-calculating-voluntary-turnover/m-p/3223080#M117911 <P>Usually fact table refers to your own table fact table could be like fkr examlle your 'turnovertable' or 'salestable'</P><P>&nbsp;</P><P>When you see fact table this what it means while if someone uses dimtable</P><P>&nbsp;</P><P>It usually means like a calendar table or your seperate product table usually it has 1 id only</P><P>&nbsp;</P><P>Dim tables are used for better relationship building so you can use dim table like a calendar table to form a relationship with multiple fact tables like example sales table and quantitity table and cost table...</P><P>&nbsp;</P><P>So in your case, switch facttable with the name of your table</P> Sat, 06 May 2023 06:34:39 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Help-with-calculating-voluntary-turnover/m-p/3223080#M117911 eliasayyy 2023-05-06T06:34:39Z Re: Help with calculating voluntary turnover https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Help-with-calculating-voluntary-turnover/m-p/3223253#M117933 <P>Ah, okay thanks I'll try that!</P><P>&nbsp;</P> Sat, 06 May 2023 14:50:23 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Help-with-calculating-voluntary-turnover/m-p/3223253#M117933 Datachef 2023-05-06T14:50:23Z Re: Help with calculating voluntary turnover https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Help-with-calculating-voluntary-turnover/m-p/3223257#M117934 <P>I feel like we are getting close but this VAR fields are coming up with errors. See screenshot belwo.</P><P><img /></P><P>&nbsp;</P> Sat, 06 May 2023 15:10:05 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Help-with-calculating-voluntary-turnover/m-p/3223257#M117934 Datachef 2023-05-06T15:10:05Z Re: Help with calculating voluntary turnover https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Help-with-calculating-voluntary-turnover/m-p/3223266#M117937 <P>In your formula where result is,</P><P>Instead of</P><P>Var result = Calculate(SUM('SAP-turnover-final[personnel no.])</P><P>&nbsp;</P><P>You need to do</P><P>&nbsp;</P><P>Var result = Calculate(SUM('SAP-turnover-final[personnel no.]),-T)</P><P>&nbsp;</P><P>RETURN</P><P>result</P><P>&nbsp;</P><P>You forgot to include return result</P><P>&nbsp;</P> Sat, 06 May 2023 15:33:53 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Help-with-calculating-voluntary-turnover/m-p/3223266#M117937 eliasayyy 2023-05-06T15:33:53Z Re: Help with calculating voluntary turnover https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Help-with-calculating-voluntary-turnover/m-p/3223303#M117942 <P>Okay, yes that got me a bit further!</P><P>This message is still coming up&nbsp;</P><H1>The Expression Refers to Multiple Columns. Multiple Columns Cannot Be Converted to a Scalar Value.</H1><P><img /></P><P>&nbsp;</P> Sat, 06 May 2023 18:36:59 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Help-with-calculating-voluntary-turnover/m-p/3223303#M117942 Datachef 2023-05-06T18:36:59Z Re: Help with calculating voluntary turnover https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Help-with-calculating-voluntary-turnover/m-p/3223309#M117944 <P>In your var result your forgot to put a comma after the sum expression so sum('your table'[your column]),_T) not = T its ,_T this is because calculate expression refers to your expression then comma then filters&nbsp;</P><P>Since _T is the filter you made above in the var so the exoression is your sum then the filter is _T</P> Sat, 06 May 2023 19:26:16 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Help-with-calculating-voluntary-turnover/m-p/3223309#M117944 eliasayyy 2023-05-06T19:26:16Z