cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dbenson
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 ACustomer BCustomer CTotal
Employee A1 1 1
Employee B1 11
Employee C 1 1
Employee D  11
1 ACCEPTED SOLUTION

@dbenson

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

Follow the steps outlined below...

Unpivot and Matrix.gif

Good Luck! Smiley Happy

Let me know if you have any questions!

View solution in original post

10 REPLIES 10
Phil_Seamark
Microsoft
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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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.  

Hi @dbenson,

 

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

@Phil_Seamarkanything new to announce? Smiley Wink or not yet?

 

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

Matrix Data.png

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! Smiley Happy

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.

@dbenson

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

Follow the steps outlined below...

Unpivot and Matrix.gif

Good Luck! Smiley Happy

Let me know if you have any questions!

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
Community Champion
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

@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!  

 

 

 Screenshot.png

Nice work.  Well done!


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.