Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

filtering not by the filter itself

Hi,

 

I have a sample data here:

 

        Product             Year   Color   Sales    Class

Pen2010Red764Stationary
Pencil2010Green977Stationary
Paper2010Red249Stationary
Ruler2010Blue971Stationary
Sticker2010Red462Stationary
Envelope2010Green636Stationary
Cardboard2010Red700Stationary
Binder2010Blue580Stationary
Memo2010Blue332Stationary
Notebook2010Red613Stationary
Refillpad2010Green123Stationary
Pen2011Green874Stationary
Pencil2011Blue270Stationary
Paper2011Red643Stationary
Ruler2011Red456Stationary
Sticker2011Red292Stationary
Envelope2011Blue551Stationary
Cardboard2011Green117Stationary
Binder2011Red86Stationary
Memo2011Red416Stationary
Notebook2011Green709Stationary
Refillpad2011Green874Stationary
Pen2012Red487Stationary
Pencil2012Red398Stationary
Paper2012Red445Stationary
Ruler2012Blue115Stationary
Sticker2012Red488Stationary
Envelope2012Blue330Stationary
Cardboard2012Blue900Stationary
Binder2012Blue567Stationary
Memo2012Green524Stationary
Notebook2012Red877Stationary
Refillpad2012Blue215Stationary
Fridge2010Blue315Appliance
Washing Machine2010Green151Appliance
Stove2010Red254Appliance
Dishwasher2010Blue846Appliance
Boiler2010Blue354Appliance
Toaster2010Red48Appliance
Fridge2011Blue312Appliance
Washing Machine2011Red41Appliance
Stove2011Red123Appliance
Dishwasher2011Green478Appliance
Boiler2011Red54Appliance
Toaster2011Red65Appliance
Fridge2012Blue98Appliance
Washing Machine2012Blue348Appliance
Stove2012Blue786Appliance
Dishwasher2012Green843Appliance
Boiler2012Green25Appliance
Toaster2012Red489Appliance

 

Is it possible to create a measure, such that when I filter on the product, it returns the sales of all the product with the same color in 2010, but not the sales of that product itself? i.e. if I have a slicer of product, when I click on "Memo", the measure will give me sales of all the blue product in 2010, which is 3398.

 

Thanks for any help.

 

JC

Need Help
Tutorial Requests
  • AlB's avatar
    AlB
    7 years ago

    Hi Anonymous,

    Try this for your measure:

    Measure = 
VAR _Color =
    CALCULATE ( SELECTEDVALUE( Table1[Color] ); Table1[Year] = 2010 )
RETURN
    CALCULATE (
        SUM ( Table1[Sales] );
        ALL ( Table1[Product] );
        Table1[Color] = _Color;
        Table1[Year] = 2010
    )

    See it at work in the attached file. I've also included another measure that calculates the sales excluding the product you select in the slicer:

    Measure2 = 
VAR _Color =
    CALCULATE ( SELECTEDVALUE( Table1[Color] ); Table1[Year] = 2010 )
RETURN
    CALCULATE (
        SUM ( Table1[Sales] );
        ALL ( Table1[Product] );
        Table1[Color] = _Color;
        Table1[Year] = 2010;
        FILTER (
            ALL ( Table1[Product] );
            Table1[Product] <> SELECTEDVALUE ( Table1[Product] )
        )
    )
    Although it would probably be better to have the the year (2010 in this case) selected in a slicer than hard-coded in the measure

2 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Hi Anonymous,

    Try this for your measure:

    Measure = 
VAR _Color =
    CALCULATE ( SELECTEDVALUE( Table1[Color] ); Table1[Year] = 2010 )
RETURN
    CALCULATE (
        SUM ( Table1[Sales] );
        ALL ( Table1[Product] );
        Table1[Color] = _Color;
        Table1[Year] = 2010
    )

    See it at work in the attached file. I've also included another measure that calculates the sales excluding the product you select in the slicer:

    Measure2 = 
VAR _Color =
    CALCULATE ( SELECTEDVALUE( Table1[Color] ); Table1[Year] = 2010 )
RETURN
    CALCULATE (
        SUM ( Table1[Sales] );
        ALL ( Table1[Product] );
        Table1[Color] = _Color;
        Table1[Year] = 2010;
        FILTER (
            ALL ( Table1[Product] );
            Table1[Product] <> SELECTEDVALUE ( Table1[Product] )
        )
    )
    Although it would probably be better to have the the year (2010 in this case) selected in a slicer than hard-coded in the measure
  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    hi, Anonymous 

    For year 2010, Is it a fixed value or a variable value.

    If is a slicer, you could try this measure as below:

    Measure = var  color=CALCULATETABLE(VALUES(Table1[Color]),ALLSELECTED(Table1[Product])) return
CALCULATE(SUM(Table1[Sales]),FILTER(ALLEXCEPT(Table1,Table1[Year]),Table1[Color] in color))

    Result:

     

    and here is demo pbix, please try it.

     

    Best Regards,

    Lin