Forum Discussion

estgas's avatar
estgas
Frequent Visitor
8 years ago

Switch a visualized table column with slicer

Hi All, after reading several posts on similar topic I could not find one solving my issue.

 

I have a table with columns: Product, Subcategory, Category, Date and Sales value and would like a visualization table that can SWITCH the FIRST column between Product, Subcategory or Category, depending on a slicer, the second column is sum of Sales.

 

I saw how to build the slicer table and related usage of selectedvalue + switch functions but I am struggeling in building the measure that could switch between the columns shwowing their aggregated values.

 

Would be really thankfull for your help.
Kind regards, est gas

Need Help

4 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    Hi estgas,

     

    One choice is to unpivot your table structure. Select [Category], [SubCategory] and [Product], click the "Unpivot Columns" option. You will get a new table structure as shown in second screenshot. Drag field [Attribute] into slicer, add [Value] and [Sales] into a table visual.

     

    If you don't want to change the soucre table structure (Suppose source table is named as 'Test1'), you may need some auxilliary tables.

    'Test2'

     

    'Test3'

    Test3 =
UNION (
    SELECTCOLUMNS ( Test1, "Cat", Test1[Category], "Sales", Test1[Sales] ),
    SELECTCOLUMNS ( Test1, "Cat", Test1[SubCategory], "Sales", Test1[Sales] ),
    SELECTCOLUMNS ( Test1, "Cat", Test1[Product], "Sales", Test1[Sales] )
)

     

    Create a one to many relationship between Test2 and Test3.

     

    Insert a table visual to host data from 'Test3', add 'Test2'[Group Type] into slicer.

     

    Best regards,

    Yuliana Gu

  • estgas's avatar
    estgas
    Frequent Visitor
    8 years ago

    Hi all, many thanks for your answers although I am afraid it would be difficult to apply the proposed solutions.

     

    The problem is that my report contains a DOZEN of sheets, each sheet contains a table with a different dimension (Product, Category, Region, Month, Color, Responsible, Sales, Cost, Margin, etc) and additional dozen of columns with different calculations.

     

    I am looking to have only ONE sheet from where the user can choose the interested column dimension instead of several sheets.

     

    I was hoping to find some easy solution like this similar case https://community.powerbi.com/t5/Desktop/table-with-dynamic-values-columns/td-p/110266 that switches the columns, but do not aggregates the values because of the index column.

     

    Kind regards

    estgas