Measure returns wrong totals

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,