Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Help with wildcards (OR) (AND)

Hello!

I have the following meassure that AlexisOlson helped me built:
It sums the sales of the End Users ( 'DB'[End User]) that sold <25000 within a Quarter, Product Group and Country WHEN 'DB'[STOCK]="NOT STOCK" and when 'DB'[Stock]<>"Not Stock", it calculates the total sales (even sales >25000)

 

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

 


Now I want to see the sales <25.000 AND >10.000

The problem is that with the following meassure, it would only display the sales of 'DB'[Stock] <> "Not Stock"
Here is my meassure that does't work...

 

 

Small Sales =
VAR SmallSales =
    CALCULATE (
        [SO Net],
        ALLEXCEPT (
            'DB',
            'DB'[Year],
            'DB'[Quarter],
            'DB'[Product Group],
            'DB'[Country],
            'DB'[Stock],
            'DB'[End User]
        )
    )
RETURN
    CALCULATE (
        [SO Net],
        FILTER (
            'DB',
            'DB'[Stock] <> "Not Stock"
                || SmallSales < 25000
                && SmallSales > 10000
        )
    )



Can someone please help me and explain why it does't work?

Need Help
  • bcdobbs's avatar
    bcdobbs
    4 years ago

    Not quite sure on the background but as a direct translation of what I can see I wondered if this would work:

    Small Sales =

CALCULATE (
    [SO Net],
    FILTER (
        'DB',
        VAR SmallSales =
		    CALCULATE (
		        [SO Net],
		        ALLEXCEPT (
		            'DB',
		            'DB'[Year],
		            'DB'[Quarter],
		            'DB'[Product Group],
		            'DB'[Country],
		            'DB'[Stock],
		            'DB'[End User]
		        )
		    )
		RETURN
		    'DB'[Stock] <> "Not Stock"
		        || (SmallSales < 25000
		        && SmallSales > 10000)
    )
)

2 Replies

  • bcdobbs's avatar
    bcdobbs
    Icon for Community Champion rankCommunity Champion
    4 years ago

    It's not working because once you create SmallSales variable it is fixed. Your filter then iterates through table DB but compares a constant on each row.

     

    Do you actually want to evaluate [S0 Net] in the row context of each row in DB?

    • bcdobbs's avatar
      bcdobbs
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Not quite sure on the background but as a direct translation of what I can see I wondered if this would work:

      Small Sales =

CALCULATE (
    [SO Net],
    FILTER (
        'DB',
        VAR SmallSales =
		    CALCULATE (
		        [SO Net],
		        ALLEXCEPT (
		            'DB',
		            'DB'[Year],
		            'DB'[Quarter],
		            'DB'[Product Group],
		            'DB'[Country],
		            'DB'[Stock],
		            'DB'[End User]
		        )
		    )
		RETURN
		    'DB'[Stock] <> "Not Stock"
		        || (SmallSales < 25000
		        && SmallSales > 10000)
    )
)