Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Slicer On Measure Column

Jump to Best Answer
  • 1.  Slicer On Measure Column

    Bronze Contributor
    Posted Dec 10, 2018 06:23 PM
    Hello,

    I have been requested to create a slicer on the column LY% (see image below).  Management would like to filter by LY% column to see if the sales reps are above or below last years sales.  I thought that it would be nice to have a slicer that could do a range so management could see different scenarios such as 10% below or 20% above, etc. but then I realized I couldn't do a slicer on a measure column.  Management would also be okay with creating a filter on just "Above 0% of LY" or "Below 0% of LY" of last years sales (see Sales Filter column that I was playing around with below).

    I am kind of stumped on how to make this work.  Any help would be appreciated.

    Thanks in advanced.
    Tim


     

    ------------------------------
    Tim Stutzman
    ERP Manager
    Eugene OR
    5412844711
    ------------------------------
    Conference-PBI_200x200


  • 2.  RE: Slicer On Measure Column

    Silver Contributor
    Posted Dec 11, 2018 03:26 AM
    Dear Tim,

    I think both could be perhaps possible with following FilterFlag solution.

    In that case you could create a new what-if Parameter Slicer.
    And afterwards you create a new measure which will be used as filterflag. That measure could for example look like following:
    FilterFlag= IF(Parameter[Parameter Value] > LY %;1;0)

    Finally on visual level it is possible to filter on a measure. So you set on your table a visual level filter on this filter flag is 1.

    Hope that helps.

    Best regards,
    Martin


    ------------------------------
    Martin Frisch
    BI Developer
    Trützschler GmbH & Co. KG
    ------------------------------

    Conference-PBI_200x200


  • 3.  RE: Slicer On Measure Column

    Top Contributor
    Posted Dec 11, 2018 03:26 AM
    Edited by Vishesh Jain Dec 11, 2018 03:28 AM
    Hi @Tim Stutzman,

    Can you please try putting the measure as a visual level filter in the visual pane.

    Then you should be able to specify the values as % > 0, which will filter the entire visual.

    Thank you,​

    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------

    Conference-PBI_200x200


  • 4.  RE: Slicer On Measure Column
    Best Answer

    Top Contributor
    Posted Dec 11, 2018 04:53 AM
      |   view attached
    Hi @Tim Stutzman

    To add to the others, please see if the following helps you.

    1.       Create a custom table as below, with the bands you want (you can additionally create 10% - 30%, 30% - 50% etc);

    thumbnail image

    2. Create a measure as below:

    Sales Filter = 
    VAR Lypercent = [LY %] 
    ---------------------------------------------------------------------
    --calculate the grading based on the bands you require
    VAR salesgrading =
        SWITCH (
            TRUE (),
            Lypercent > 0, "Above 0%",
            Lypercent < 0, "Below 0%",
            Lypercent = 0, "No Change",
            BLANK ()
        ) 
    -----------------------------------------------------------------------
    --get the current selected value of the custom slicer
    -- values of this custom slicer table will be the band names you will be generating above
    VAR slicervalue =
        SELECTEDVALUE ( 'LY Change Slicer'[Change Slicer] ) 
    -----------------------------------------------------------------------
    --this variable will return blank if the currently selected slicer value is 
    --not the same as the current row calculated grading
    VAR filteredvalue =
        IF ( slicervalue = salesgrading, salesgrading, BLANK () ) 
    ----------------------------------------------------------------------
    --if a value is not selected in the slicer, then we should get all the values
    VAR finalvalue =
        IF (
            HASONEVALUE ( 'LY Change Slicer'[Change Slicer] ),
            filteredvalue,
            salesgrading
        ) 
    ----------------------------------------------------------------------
    RETURN
        --isinscope used on the sales rep value  to avoid showing the grading value in the total row
        IF (
            ISINSCOPE ( 'Sales Summary'[Sales Rep] ),
            finalvalue,
            BLANK ()
        )

    3. Put the measure in the table/matrix visual and ensure that in the visual level filter area, the above measure is filtered to show items when the value is not blank;
    thumbnail image

    You can then have a visual filtered by the selected bands:
    thumbnail image


    thumbnail image

    PFA the pbix file also for your reference.









    ------------------------------
    Gopa Kumar S

    ------------------------------

    Attachment(s)

    Conference-PBI_200x200


  • 5.  RE: Slicer On Measure Column

    Bronze Contributor
    Posted Dec 12, 2018 01:47 PM
    Thank you Gopa!  Your solution worked perfectly!

    Tim

    ------------------------------
    Tim Stutzman
    ERP Manager
    Eugene OR
    5412844711
    ------------------------------

    Conference-PBI_200x200