Counting how many employees worked on multiple customers

[dbenson]

Newbie here!  This seems like an easy question, but I'm having a hard time figuring out the proper formula here.  

I have a database with employee names, customers they worked on and how much we paid them to work on each of those customers.  I'm trying to do a simple analysis to see the names of my employees that worked on more than one customer (and to see which exact customers they are working on).  

I believe the best way to represent this is a matrix table and I was running a basic Distinctcount on the employees to show the boolean values on whether they worked on a customer or not.  That part is fine.  However, I cannot get the sum at the end to see if they did in fact do more than one customer as the grand total will default to 1 for all employees as the Distinctcount looks to  be re-running for the filter context. The below table is what I am currently seeing.  Any advice here?  

	Customer A	Customer B	Customer C	Total
Employee A	1	1		1
Employee B	1		1	1
Employee C		1		1
Employee D			1	1

[Phil_Seamark]

Hi @dbenson,

The Total is doing what you asked in providing a distinct count of Employees.

So rather than using DISTINCTCOUNT, use a SUM measure wrapped in an IF statement.

Something like Measure = IF(SUM(<something>) > 1 , 1 , 0) 

and this should sum nicely across to your Totals

[dbenson]

I tried this one out.  Since we have the pay amounts by employee by customer, I added an IF formula as follows:

if(sum(PayTable[Pay]>1,1,0)

Popped it in a matrix table and it gave me the same boolean table as the DistinctCount function.  However the totals are still defaulting to 1, even under this IF statement.  This is probably because the formula is still running and stating that if there are any pay amounts for this one employee, default the value to 1 instead of doing a sum.  

[Phil_Seamark]

Hi @dbenson,

Is there any chance you can share a small sample set of your data.  Enough to reproduce a matrix

[Sean]

Yes we need to see how the data is set up in the Table Vew!

@Phil_Seamarkanything new to announce?  or not yet?

Anyway @dbenson you can write a single measure to give you vertical and horizontal totals

In Blue Employees per Customer

In Red how many Customers each Employee works for and

In Red the GT overall Customers for the whole company (for all employees)

This is done with IF ( HASONEVALUE (....

But we need to see some sample data!

[dbenson]

Thanks for pointing this out as well.   I'm not super comfortable with understanding how the totals are working and how to use the HASONEVALUE formula.  I picked up 2 DAX books at lunch time today and so I will try to play around a bit more.  So far so good though - just doing this one report in Power BI has saved my payroll dept from running 20 steps manually in Excel.

[Sean · Accepted solution]

@dbenson

You need to UNPIVOT the data first in the Query Editor and then create the Matrix

Follow the steps outlined below...

Good Luck!

Let me know if you have any questions!

[dbenson]

Appreciate the speedy response here. That would work if I could get that table within the query editor.  Right now, I'm running the distinctcount in order to get the boolean values in a matrix table.  It's not currently in a data set that I can unpivot.  Is there some magic that has to be done in the query editor to get it there or a summarize function?  

Cheers,

[Sean]

You most likely have more than 3 customers it would be best if you Unpivot the Data!

Why can't you get in the Query Editor? Modeling Tab - Edit Queries

[dbenson]

@Sean @Phil_Seamark

Solution found!  

The issue I was having was that I was bringing in raw data from a transaction database, so hundreds of employees, customers over a million or so records.  So one employee was actually shown under the same customer a few dozen times for every week.  The table which was presented at the top was a matrix summary of the raw transaction table and so was being finnicky to work with.  

To get into the format which you showed above, I did a grouping in the queries and grouped it so that an employee would only show once per customer per week  (which is what I am applying a filter on) shown below in the screenshot (note that I just deleted the aggregation column as it was not needed - I just wanted the list of names by customer and week) .  Once that was done, doing the matrix like you showed was easy as it was just a count.  I ran a measure for the count so that I can apply a filter to remove all employees who only worked on one customer and got the exact report I needed.  Thanks for the help guys!  

 

[Phil_Seamark]

Nice work.  Well done!