Count counting some items twice

This is a classic scenario in Power BI and other data tools, where the COUNT measure counts some items twice—almost always due to the underlying data model and table relationships, not the measure itself. Let’s break down the possible reasons and how to fix it:

Why the COUNT is Incorrect

1. Duplicates in Source Table:
   If your [Actual] column or the table 'Outbound Delivery' contains duplicate rows for the same item, COUNT will count each row, even if the value is the same.

2. Many-to-Many or Incorrect Relationships:
   If your data model has many-to-many relationships, or if the 'Outbound Delivery' table is related to other tables in a way that creates duplicate rows in the result (for example, due to improper joins or ambiguous relationships), the COUNT will sum up those duplicates.

3. Expanded Data in Visual:
   If your visual’s granularity is higher than the actual base data, (e.g., you are grouping or joining with another table), the COUNT will reflect that expanded set.

How To Diagnose

• Check the Data Table:
  Go to Data view and use "Remove Duplicates" on the [Actual] column. See if duplicate rows exist.
• Look at Relationships:
  In Model view, check all relationships connected to 'Outbound Delivery'. If there are more than one, or if there are bidirectional or many-to-many relationships, these can multiply your row count.
• Drill Down:
  Filter a problematic value (e.g., 2.00) and check all rows—do you see multiple rows with the same value? If yes, that’s your source of duplication.

How To Fix

A. If you want to count unique values (distinct):

Use the DISTINCTCOUNT function in DAX:

Count of Unique Outbound Deliveries = DISTINCTCOUNT('Outbound Delivery'[Actual])

This will count each unique [Actual] value only once.

B. If you want to count unique rows:

If you have a row ID or unique key, use:

Count of Unique Rows = DISTINCTCOUNT('Outbound Delivery'[RowID])

C. To find the duplicates:

You can add a calculated column:

Duplicate Check = 
CALCULATE(
    COUNTROWS('Outbound Delivery'),
    ALLEXCEPT('Outbound Delivery', 'Outbound Delivery'[Actual])
)

Then filter where [Duplicate Check] > 1 to find which values are duplicated.

General Advice

• The COUNT measure is not “wrong”—it simply counts all rows, including duplicates created by your data structure or relationships.
• Use DISTINCTCOUNT to get unique items.
• Always check relationships and data granularity in your model.

If you share a sample of your data model or clarify the relationships, I can provide even more tailored advice. But with these steps you should be able to pinpoint and fix the double-counting issue.

Let me know if you need detailed step-by-step instructions!
translation and formatting supported by AI