Measure returns wrong totals

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,