Switch a visualized table column with slicer

[estgas]

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

[estgas]

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

[v-yulgu-msft]

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

[Daniil]

You can try this: https://xxlbi.com/blog/dynamic-hierarchies-in-power-bi/

[stretcharm]

Try using Bookmarks and Selection visability.

https://docs.microsoft.com/en-us/power-bi/desktop-bookmarks

https://www.youtube.com/watch?v=_Afcj8mT5_Q

https://databear.com/power-bi-bookmarks/