cancel
Showing results for
Did you mean:
Regular Visitor

## Counting how many employees worked on multiple customers

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
1 ACCEPTED SOLUTION
Community Champion

@dbenson

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

Good Luck!

Let me know if you have any questions!

10 REPLIES 10
Microsoft

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

Proud to be a Datanaut!

Regular Visitor

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.

Microsoft

Hi @dbenson,

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

Proud to be a Datanaut!

Community Champion

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!

Regular Visitor

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.

Community Champion

@dbenson

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

Good Luck!

Let me know if you have any questions!

Regular Visitor

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,

Community Champion

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

Regular Visitor

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!

Microsoft

Nice work.  Well done!

Proud to be a Datanaut!

Announcements

#### Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors