Help with filter context!

[Anonymous]

Hello!

I want the code to sum the sales of the End Users ( 'DB'[End User]) that sold <25000 within a Quarter, Product Group and Country.
I have the following code:

Small Sales = 
SUMX (
    GROUPBY (
        'DB',
        'DB'[Year],
        'DB'[Quarter],
        'DB'[Product Group],
        'DB'[Country],
        'DB'[Stock],
        'DB'[End User]
    ),
    IF ( 'DB'[Stock] <> "Not Stock" || [Sales] > 25000, [Sales] )
)

The problem is that when I put that in a Matrix...:

The sum of the individual week totals (33M) is greater than the Total (27,8M) in the matrix.
I believe is because it is on a weekly filter context, so it won't evaluate if QUARTERLY SALES are <25000, but if WEEKLY SALES are <25000

How do I fix it?

I need to display a weekly value of the Small Sales, but the code needs to use all the weeks in the quarter to determine if sales are <25000 or not

Please help!

Thank you very much!

[amitchandak]

@Anonymous , try measure like

Var _sales = calculate([Sales], filter(allselected('DB'), 'DB'[Year] = max('DB'[Year]) && 'DB'[Quarter] max('DB'[Quarter]) &&
'DB'[Product Group] =max('DB'[Product Group]) && 'DB'[Country] = max('DB'[Country]) && DB'[Stock] =max(DB'[Stock]) && 'DB'[End User] =max('DB'[End User]))
return
calculate([Sales], filter('DB', 'DB'[Stock] <> "Not Stock" || _sales >25000 ) )

[AlexisOlson · Accepted solution]

This doesn't seem like it should work (even after correcting the various syntax errors) since _sales is a constant variable.

Maybe we can keep the calculate inside the FILTER like this?

CALCULATE (
    [Sales],
    FILTER (
        'DB',
        'DB'[Stock] <> "Not Stock" ||
         CALCULATE (
            [Sales],
            ALLEXCEPT (
                'DB',
                'DB'[Year],
                'DB'[Quarter],
                'DB'[Product Group],
                'DB'[Country],
                'DB'[Stock],
                'DB'[End User]
            )
        ) < 25000
    )
)