How to do distinct employee count by distinct ID

[Anonymous]

I have 2 formulas that make up this chart:

And they are:



The 'Employee Range Size' is being used as the Legend and the values are a distinct count on the company ID. I also have this same information separated into tables such as:



So the 124 is doing a distinct count on the company ID which is what I want. My issue is the Employee Count column. My total at the bottom for that column is a bit skewed because as you can see for company ID 0340, it is listed twice because it has a payroll service and a cbiz ems service, so instead of just counting 214 once, it is doubling it to 428. How can I set this up to only count 214 one time? For whatever reason when I remove the 'Service' Column from the table, the grand total goes up almost 20,000 for some reason I don't understand, so that is not the solution. If there is a better way to set all of this up, I am open to any suggestions .

[v-xjiin-msft]

@Anonymous

In your scenario, there exists duplicated Employee Count for each Company ID because of the Service. When you put Employee Count field in the Values of a Pie Chart, the duplicated has been summed automatically. And you don’t want the duplicated be summed. Right?

First, your expressions for Employee Counts and Employee Range Size are both right. The reason for your issue is that charts cannot distinguish if the values are duplicated. By default it will sum them all automatically. To modify this, you just need to change the aggregate function for Employee Count from Sum to Max.

Thanks,
Xi Jin.

[Anonymous]

Mine doesn't look like it's set up the same way. My 'Employee Counts' is a measure I created specifically for counting the number of times an employee id appears for a company id. I do not have the option to change this to Maximum. On my chart My Legend is "Employee Range Size" and I'm just using a distinct count on the company ID as my values to capture how many companies fall within each range. Here are the pieces:

Then for the corresponding tables, these are the attached pieces:

So the 'Employee Count' column you see on each table is just the ID field from the Employees table and is doing a Count on that column:

And I have filters on each table referencing my 'Employee Counts' measure I created so it returns the same values represented on my graph:

[Greg_Deckler]

You have a classic "My Measure Total is Wrong" situation:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

[Anonymous]

Thank you smoupre! However, I am still unsure of how to apply that logic to my situation. Is there any way to have the table read all the company ID's within that table and count the Employee Count for the given Company ID just once?