Divide by Sum of 2 columns with Filter

[Anonymous]

I have a measure that divides sum of 2 columns (from 2 tables). However I need to include a filter in the measure but not sure how to implement this i.e. 'Exception Detail'[Scan Type] <> "No Exception Scan".

Appreciate any help please. Thanks.

%ExcepScan = DIVIDE(SUM('Exception Detail'[COUNT(*)]),SUM('Exception Summary'[NUMBER_OF_PARCELS_NOT_ON_TIME]))

[AlB]

@Anonymous 

It makes sense that the result changes if you add more filters. You'll have to explain what exact behaviour you want in those cases, and what the expected result is

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

I was expecting the result to be the same for both tables no matter what filters are applied since they both reference the same dataset. Also both tables Exception Scan sum will always be the same, the only difference is that the Details table doesn't have a 'Not on Time' column. Example from the screenshot earlier, both tables showing same number of Exception scans captured = 3, Not on time = 12, so 3/12 = 25%

The only difference here is that the Exception Details table doesn't have a 'Not on Time' column, instead its referencing the Exception Summary table 'Not on Time' column.

[AlB]

Hi @Anonymous 

How  about

%ExcepScan =
CALCULATE (
    DIVIDE (
        SUM ( 'Exception Detail'[COUNT(*)] ),
        SUM ( 'Exception Summary'[NUMBER_OF_PARCELS_NOT_ON_TIME] )
    ),
    'Exception Detail'[Scan Type] <> "No Exception Scan"
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Hi @AlB, thank you that works with date filters only BUT when I apply further filters, the %ExcepScan measure returns 0% as per screenshot below. How to overcome this without explicitly specifying filters in the measure?

Here's link to pbix file fyi