locked
slicer to switch between column RRS feed

  • Question

  • Hello PP gurus..

    Today I am coming with the case "how to do slicer to switch between multiple columns"?

    My data model is complex and quite complicated so I have prepared a sample.

    I have two tables (table.1 and table.2):

    table.1
    PRODUCT ID CATEGORY 2016 CE CATEGORY 2017 CE CATEGORY 2017 GLOBAL ...
    1 PC FC BODY ...
    2 CO CO COLOR ...
    3 SC FC FACE ...
    4 FR FR FRAGRANCE ...
    5 SC WB TOILETRIES ...
    6 PC FC BODY ...
    7 CO CO COLOR ...
    8 SC FC FACE ...
    9 FR FR FRAGRANCE ...
    10 SC WB TOILETRIES ...
    ... ... ... ... ...

    table.2
    PRODUCT ID SALES $ ...
    1 1000 ...
    2 1001 ...
    3 1002 ...
    4 1003 ...
    5 1004 ...
    6 1005 ...
    7 1006 ...
    8 1007 ...
    9 1008 ...
    10 1009 ...
    ... ... ...

    table.1 and table.2 are related ones.

    In table.1 I have got a unique product id, many other columns and three different product categorizations:

    CATEGORY 2016 CE

    CATEGORY 2017 CE

    CATEGORY 2017 GLOBAL

    I would like to allow user to choose on slicer one of those categorizations and get proper sales figures:

    How to do a slicer like above to properly filter categories and sales?


    Wednesday, October 19, 2016 1:22 PM

Answers

  • This can be done fairly easily using a helper table. This table should contain your three categories and a Code column:

    Category     Code

    Category 2016 CE     1

    Category 2017 CE     2

    Category 2017 GLOBAL     4

    The column Category is used in a slicer, and the item selected can be determined by calculating SUM(Category[Code]). Next, create a measure using SWITCH:

    Result:= SWITCH(SUM(Category[Code]), 1, [ResultCat2016CE], 2, [ResultCat2017CE], 4, [ResultCat2017GLOBAL])

    Here, [ResultCat2016CE], [ResultCat2017CE], and [ResultCat2017GLOBAL] are measures that return the required result for each category.


    Thursday, October 20, 2016 10:32 AM
    Answerer

All replies

  • Hi Bartek Wachocki,

    Unfortunately, it is impossible to satisfy your requirement if you want to use slicer in Pivot table.

    Slicers are easy-to-use filtering components that contain a set of buttons that enable you to quickly filter the data in a PivotTable report, without the need to open drop-down lists to find the items that you want to filter. The items in Slicer should be the value in the column used as slicer. For example, I insert a slicer includes Category 2016 CE, it will return the result as follows. For more details about slicer, please review the article.

     
    I try to create another table includes three categories, then I insert a slicer like your screenshot.  We must create a relationship to other two linked table if we want to use the slicer,  while it needs row to link tables. So it’s not appropriate.

     

    Finally, I create a Pivot table which include one category as rows, sales as Values,  and get the expected results. So you can allow user to choose on the categorizations wanted and get proper sales figures in Pivot table.



    Best Regards,
    Angelia


    Thursday, October 20, 2016 9:05 AM
  • This can be done fairly easily using a helper table. This table should contain your three categories and a Code column:

    Category     Code

    Category 2016 CE     1

    Category 2017 CE     2

    Category 2017 GLOBAL     4

    The column Category is used in a slicer, and the item selected can be determined by calculating SUM(Category[Code]). Next, create a measure using SWITCH:

    Result:= SWITCH(SUM(Category[Code]), 1, [ResultCat2016CE], 2, [ResultCat2017CE], 4, [ResultCat2017GLOBAL])

    Here, [ResultCat2016CE], [ResultCat2017CE], and [ResultCat2017GLOBAL] are measures that return the required result for each category.


    Thursday, October 20, 2016 10:32 AM
    Answerer
  • Hi Angelina

    I appreciate your help. It is bad to hear there is no right solution. 

    Filtering by slicer allows to control multiple pivots or whole my report. Thats what I need.

    I will try to rearrange data. Maybe duplicate table.1 and get three ones with each category version in separate table. Then connect each one to my table.2 and to the one similar to yours but with added category column



    Thursday, October 20, 2016 11:14 AM
  • Hi Michiel

    Thank for your feedback. Your idea is right when it comes to switching between measures. My measure is always:

    SALES=SUM(SALES $) 

    Here is another example from my real data what I am looking for

    Sales should be inside pivot table.  Currently i have added it manually

    


    Thursday, October 20, 2016 12:10 PM
  • Hi Bartek Wachocki,

    Glad to hear that your issue got solved. Please mark corresponding reply which will help find the solution easily.

    Thank you very much.

    Best Regards,
    Angelia
    Friday, October 21, 2016 1:32 AM
  • Maybe my answer was not clear. In fact it is not solved. 

    Michael's solution is not working for my case. Or maybe I do not know how to do "result" measure to work right

    Friday, October 21, 2016 8:33 AM
  • Your measures could look like

    CALCULATE(SUM(Sales[Sales]), Category_version = "CATEGORY 2016 CE")

    But I have the feeling that your data looks a bit different from what's in your first post. Do you have a screenshot of the diagram?

    Friday, October 21, 2016 4:21 PM
    Answerer
  • Guys. Sorry for late answer

    Finally I solved with rearranging table 1:

    table.1
    PRODUCT ID CATEGORY CATEGORY VERSION     ....
    1 PC CATEGORY 2016 CE
    2 CO CATEGORY 2016 CE
    3 SC CATEGORY 2016 CE
    4 FR CATEGORY 2016 CE
    5 SC CATEGORY 2016 CE
    6 PC CATEGORY 2016 CE
    7 CO CATEGORY 2016 CE
    8 SC CATEGORY 2016 CE
    9 FR CATEGORY 2016 CE
    10 SC CATEGORY 2016 CE
    ... ... CATEGORY 2016 CE
    1 FC CATEGORY 2017 CE
    2 CO CATEGORY 2017 CE
    3 FC CATEGORY 2017 CE
    4 FR CATEGORY 2017 CE
    5 WB CATEGORY 2017 CE
    6 FC CATEGORY 2017 CE
    7 CO CATEGORY 2017 CE
    8 FC CATEGORY 2017 CE
    9 FR CATEGORY 2017 CE
    10 WB CATEGORY 2017 CE
    ... ... CATEGORY 2017 CE
    1 BODY CATEGORY 2017 GLOBAL
    2 COLOR CATEGORY 2017 GLOBAL
    3 FACE CATEGORY 2017 GLOBAL
    4 FRAGRANCE CATEGORY 2017 GLOBAL
    5 TOILETRIES CATEGORY 2017 GLOBAL
    6 BODY CATEGORY 2017 GLOBAL
    7 COLOR CATEGORY 2017 GLOBAL
    8 FACE CATEGORY 2017 GLOBAL
    9 FRAGRANCE CATEGORY 2017 GLOBAL
    10 TOILETRIES CATEGORY 2017 GLOBAL
    ... ... CATEGORY 2017 GLOBAL

    This solution has one disadvantage - data storage increased 3x

    @ Michael. Your suggestion can do this case too. But instead of one sales measure I have to create 3 for each category version and a helper table. 

    Wednesday, November 9, 2016 9:37 AM