This happens because a Calculated Column adds a value to every row.
If a Name has 2 rows (e.g., 1 for "Paris" and 1 for "India"), the column marks both as "Centralized". When you drag that column to a visual and it sums them up, you get 2 instead of 1.
To fix this, you must use a Measure that looks at the distinct Names, not the individual rows.
The Solution: Use These Measures
Delete the calculated column (or stop using it in your visual) and use these Measures instead. They use SUMX and VALUES to ensure each Name is counted only once, regardless of how many rows it has.
1. Centralized Count (Measure)
This calculates unique Names that have at least one centralized team.
Centralized Count =
VAR CentralizedNames =
SUMX (
VALUES ( 'YourTable'[Name] ), -- Iterate through unique Names only
VAR HasCentralizedTeam =
CALCULATE (
COUNTROWS ( 'YourTable' ),
'YourTable'[Team] IN { "Paris", "Germany", "Poland" }
)
RETURN
IF ( HasCentralizedTeam > 0, 1, 0 )
)
RETURN
CentralizedNames
2. Non-Centralized Count (Measure)
This calculates unique Names that have zero centralized teams.
Non Centralized Count =
VAR NonCentralizedNames =
SUMX (
VALUES ( 'YourTable'[Name] ), -- Iterate through unique Names only
VAR HasCentralizedTeam =
CALCULATE (
COUNTROWS ( 'YourTable' ),
'YourTable'[Team] IN { "Paris", "Germany", "Poland" }
)
RETURN
IF ( HasCentralizedTeam = 0, 1, 0 ) -- Only count if NO centralized team found
)
RETURN
NonCentralizedNames
Why this works
* VALUES ( 'YourTable'[Name] 😞 Creates a temporary list of unique names.
* SUMX: Goes through that unique list one by one.
* The Logic: It checks "Does this specific Name have a row in Paris, Germany, or Poland?"
* If Yes: It scores it as a 1 for Centralized.
* If No: It scores it as a 1 for Non-Centralized.
* Result: Even if a Name has 50 rows, it is only evaluated and counted once.
