Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Help with filter context!

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!

 

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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
    )
)

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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's avatar
      AlexisOlson
      Super User
      4 years ago

      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
    )
)