Measure returns wrong totals

[Justas4478]

Hi, I have this measure:

CALCULATE(

    DISTINCTCOUNT('Outbound Delivery Document'[DocumentNumber]),

    REMOVEFILTERS('Date'[Date]),

    TREATAS(VALUES('Outbound Delivery'[OutboundDeliveryDocumentKey]), 'Outbound Delivery Document'[OutboundDeliveryDocumentKey])

)

It works well for most part, but after checking more granular data in some cases it returns wrong totals.

I had to create this measure since I can only use measures dues to data source being in live connection.
I tried to do some changes to distinctcount or remove filters but that did not seam to affect results at all.

[DataNinja777]

Hi @Justas4478 ,

The issue you're experiencing stems from how DISTINCTCOUNT combined with REMOVEFILTERS behaves differently at the row level versus the total level in a matrix. In your measure, you're removing the date filter context and applying TREATAS to align the key from the Outbound Delivery table with the Outbound Delivery Document table. This works correctly for individual rows but leads to incorrect totals because Power BI does not sum the results of each row to get the total. Instead, it recalculates the whole formula in the context of the total, which introduces overcounting or undercounting due to a wider filter context.

To fix this, you can rewrite the measure using SUMX to explicitly iterate over each row-level context (in this case, the date), calculate the distinct count for each, and sum those results. This forces the total to be the sum of the individual row-level values, thereby matching what you see in each row. Here's how the adjusted measure would look:

Correct Total Measure =
SUMX(
    VALUES('Date'[Date]),
    CALCULATE(
        DISTINCTCOUNT('Outbound Delivery Document'[DocumentNumber]),
        TREATAS(
            VALUES('Outbound Delivery'[OutboundDeliveryDocumentKey]),
            'Outbound Delivery Document'[OutboundDeliveryDocumentKey]
        )
    )
)

This approach ensures that each date’s distinct count is calculated in isolation and then summed, resolving the total discrepancy you observed. If your rows are based on a different field like Floor or System instead of Date, you can adjust the iterator in VALUES(...) to reflect the appropriate field.

Best regards,

[Justas4478 · Accepted solution]

@DataNinja777 I think I manage to find solution that solved my problem.
I am just unsure how does it work.
I know one thing that it looks like customer sum was a problem and not floors.
But I still dont understand why I had to change

DISTINCTCOUNT('Outbound Delivery Document'[DocumentNumber])

to 

DISTINCTCOUNT('Outbound Delivery'[OutboundDeliveryDocumentKey])
and 

VALUES('Outbound Delivery'[OutboundDeliveryDocumentKey]),
            'Outbound Delivery Document'[OutboundDeliveryDocumentKey]

to 

TREATAS(

            VALUES('Outbound Delivery Document'[OutboundDeliveryDocumentKey]),

            'Outbound Delivery'[OutboundDeliveryDocumentKey])

And how those changes solved the problem.

[DataNinja777]

Hi @Justas4478 ,

The issue occurs because Power BI evaluates measures differently at the total level than at the row level. In your original formula, you used DISTINCTCOUNT('Outbound Delivery Document'[DocumentNumber]) combined with REMOVEFILTERS('Date'[Date]), which removes the date context entirely when calculating totals. As a result, the total calculation overextends its scope and includes more records than it should. Although the calculation works fine per row—such as per floor or date—it misbehaves at the total level since Power BI doesn't sum the individual rows but instead re-evaluates the formula in the broader total context.

CALCULATE(
    DISTINCTCOUNT('Outbound Delivery Document'[DocumentNumber]),
    REMOVEFILTERS('Date'[Date]),
    TREATAS(
        VALUES('Outbound Delivery'[OutboundDeliveryDocumentKey]),
        'Outbound Delivery Document'[OutboundDeliveryDocumentKey]
    )
)

You fixed the issue by flipping the direction of the TREATAS function and counting the distinct keys in the 'Outbound Delivery' table instead. By using DISTINCTCOUNT('Outbound Delivery'[OutboundDeliveryDocumentKey]) and reversing the TREATAS to filter 'Outbound Delivery' based on the document keys from 'Outbound Delivery Document', you ensured that the filtering aligned with the correct context and grain of your visual. This also eliminated the need to use REMOVEFILTERS, which had caused the overcounting in the total.

CALCULATE(
    DISTINCTCOUNT('Outbound Delivery'[OutboundDeliveryDocumentKey]),
    TREATAS(
        VALUES('Outbound Delivery Document'[OutboundDeliveryDocumentKey]),
        'Outbound Delivery'[OutboundDeliveryDocumentKey]
    )
)

This approach worked because 'Outbound Delivery' is likely the table with the correct level of detail for your matrix (e.g., customer, floor, and date granularity). Filtering it based on the 'Outbound Delivery Document' keys ensured that the distinct count was evaluated correctly within each row and also aggregated correctly at the total level without any need for SUMX or date manipulation.

Best regards,

[Justas4478]

@DataNinja777 I just checked and it does still require SUMX to show correct sum.

[Justas4478]

@DataNinja777 I did try your suggestion.
But I got same results

When I tried to change data value to floor it only affected grand total and result was 2000+ which is even more off then original.