SUM doesn't show correct values with calculate/filter

[Tavi_]

Hi,

I'm calculating the production output in KG, but when I drill down the formula shows values on item will there is no data on those items.

I'm using thise dax formula:

CALCULATE(SUM('[FACT] Material Documents'[Quantity in Weight in KG]), '[FACT] Material Documents'[Movement Type] IN {"101", "102", "261", "262"})

For order 5059295 the formula shows values in movement type 261 while there is no data on 261. And for order 5059296 it takes shows the sum of movement type 101 and 206.

Rows order and movement type are form the same tabel.

What am I doing wrong.

[Anonymous]

Thanks for the reply from DataNinja777 , please allow me to provide another insight:

Hi, @Tavi_ 
Thanks for reaching out to the Microsoft fabric community forum.

Could you please let us know if DataNinja777’s response resolved your issue? If it did, kindly accept it as the solution.

Based on your description, if your requirement is only to filter by Movement Type, the most effective solution is to use the following measure as a filter. For the values section, you can directly use the Quantity in Weight in KG column:

MEASURE =
IF (
    MAX ( '[FACT] Material Documents'[Movement Type] )
        IN { "101", "102", "261", "262" },
    1,
    0
)

However, this is not conducive to your later call, if the problem has not been solved, you are welcome to share the data with us, we recommend you to use github, please pay attention to remove sensitive information.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataNinja777 · Accepted solution]

Hi @Tavi_ ,

It looks like your DAX measure is summing values from [Quantity in Weight in KG] where [Movement Type] is in the specified set ({"101", "102", "261", "262"}), but it's still showing unexpected results when drilling down by order and movement type. One possible reason is that [Movement Type] might not be correctly related to [Order] in your data model, which could result in the measure displaying values even when there is no actual data for that movement type under the given order. Ensuring that [Movement Type] and [Order] are properly linked in your data model is crucial for the expected results.

Another potential issue is that Power BI still shows rows for orders even if there is no relevant [Movement Type] when drilling down. To ensure the filtering is applied correctly, you can explicitly use the FILTER function within CALCULATE to enforce the condition at the row level:

Production KG =
CALCULATE(
    SUM('[FACT] Material Documents'[Quantity in Weight in KG]),
    FILTER(
        '[FACT] Material Documents',
        '[FACT] Material Documents'[Movement Type] IN {"101", "102", "261", "262"}
    )
)

Additionally, if [Movement Type] is stored as a number rather than text, using quotes inside the IN condition may cause mismatches. If [Movement Type] is numerical, the correct version of the formula would be:

Production KG =
CALCULATE(
    SUM('[FACT] Material Documents'[Quantity in Weight in KG]),
    '[FACT] Material Documents'[Movement Type] IN {101, 102, 261, 262}
)

Another factor to consider is whether blank or null values are causing movement types to appear incorrectly in drill-down views. You can use HASONEVALUE to prevent Power BI from showing values when [Movement Type] is not explicitly filtered:

Production KG =
IF(
    HASONEVALUE('[FACT] Material Documents'[Movement Type]),
    CALCULATE(
        SUM('[FACT] Material Documents'[Quantity in Weight in KG]),
        '[FACT] Material Documents'[Movement Type] IN {"101", "102", "261", "262"}
    ),
    BLANK()
)

If duplicates in [FACT] Material Documents] are causing inflated values, using DISTINCT in SUMX can help ensure that only unique values are considered:

Production KG =
SUMX(
    DISTINCT('[FACT] Material Documents'),
    CALCULATE(
        SUM('[FACT] Material Documents'[Quantity in Weight in KG]),
        '[FACT] Material Documents'[Movement Type] IN {"101", "102", "261", "262"}
    )
)

To diagnose the issue further, check whether [Movement Type] and [Order] are correctly related in your data model. Also, add [Movement Type] as a filter in a table visual to confirm whether unexpected rows exist. Testing variations of the measure with different filtering approaches can help pinpoint why movement type 261 appears when it shouldn’t.

Best regards,