Tricky Counts

[Anonymous]

I have this data:

 

So a person can be either an EMS or Payroll account manager. To attempt to get the counts of the [Value] column for a person, I merged the EMS and Payroll columns, then any value that contained "Ana" or "Ashton" were grouped together to return the total count for each person.

My issue is, looking closer at the data, I realized some values may be assigned to a person when they shouldn't be, as indicated by the highlighted rows above. The "Strongly Disagree" values currently apply to Ana Reveles, but the [Attribute] column specifies that those values are associated with questions about the EMS account manager instead of Payroll. So those values in reality shouldn't apply to her since she's only the Payroll Account manager. Is there any way to account for this? If not I think I would have to have one table for EMS, one table for Payroll, and just append the tables together to accurately catch the counts for each person. Thanks!

[v-yulgu-msft]

Hi @Anonymous,

---

To attempt to get the counts of the [Value] column for a person, I merged the EMS and Payroll columns, then any value that contained "Ana" or "Ashton" were grouped together to return the total count for each person. 

I noticed that both "Ana" and "Ashton" are only existing in [Payroll] field, why do you need to merge the EMS and Payroll columns? To group data that contains "Ana" or "Ashton", it looks like you only need to take [Payroll] into account.

---

The "Strongly Disagree" values currently apply to Ana Reveles, but the [Attribute] column specifies that those values are associated with questions about the EMS account manager instead of Payroll. So those values in reality shouldn't apply to her since she's only the Payroll Account manager. Is there any way to account for this? 

---

I am not very clear about above message. How do you want to deal with these data? What is the desired result of [Value] count?

Regards,

Yuliana Gu

[Anonymous · Accepted solution]

Hi @v-yulgu-msft Ashton and Ana are also EMS account managers, it's just not in the above screenshot. There's thousands of rows. But I went with what I was originally saying my alternative may have to be. I took just the payroll questions and managers in one table, the ems questions and managers in another table. I filtered both tables by Ashton and Ana only then appended them together and renamed the [Payroll]and [EMS] fields to just read [Account Manager]. The EMS and Payroll prefixes on the [Attribute] column will let me know if responses are coming from payroll or ems questions.

So now I'm able to capture the total count of [Value] from Ana and Ashton whether they were a payroll or ems account manager:

[Farhan75]

Hi. If the first word of the [Attribute] column is what defines who you want to count the value against, can you do something like this? Which would give you a column that contains either the name of the Payroll manager or the EMS.

if Text.Start([Attribute],Text.PositionOf([Attribute]," "))="Payroll" then [Payroll] else [EMS]

You may want another column that states which one it is:

Text.Start([Attribute],Text.PositionOf([Attribute]," "))