Toggle columns to be group by

[itsmeanuj]

Hi, I have a dataset of customers by product where PROD_CD is a subset of POD_GRP. Below is the sample.

Input  Data

Customer	PROD_CD	PROD_GRP	Month1	Month2	Month3
A1	Product1.A	Product1	54	18	21
A1	Product1.B	Product1	10	5	7

I want to create a table visual with the toggle (slicer or anything else) option to switch between the columns on which Monthly sales can be grouped by. Here is the example of the output view.

Output table view (Toggle 1)

Customer	Product	Month1	Month2	Month3
A1	Product1.A	54	18	21
A1	Product1.B	10	5	7

Output table view (Toggle 2)

Customer	Product	Month1	Month2	Month3
A1	Product1	64	23	28

[v-yulgu-msft · Accepted solution]

Hi @itsmeanuj ,

In Query Editor mode, choose columns [PROD_CD] and [PROD_GRP], click "unpivot columns".

Rename columns.

Save above changes. In report view, drag [Slicer Items] into slicer and other fields into table visual.

Best regards,

Yuliana Gu

[PattemManohar]

@itsmeanuj  Please try this as a New Table

Test233Out = 
VAR _GroupByProdCD = SUMMARIZE(Test233Grouping,Test233Grouping[Customer],Test233Grouping[PROD_CD],"Month1",SUM(Test233Grouping[Month1]),"Month2",SUM(Test233Grouping[Month2]),"Month3",SUM(Test233Grouping[Month3]),"Type","Product Code")
VAR _GroupByProdGRP = SUMMARIZE(Test233Grouping,Test233Grouping[Customer],Test233Grouping[PROD_GRP],"Month1",SUM(Test233Grouping[Month1]),"Month2",SUM(Test233Grouping[Month2]),"Month3",SUM(Test233Grouping[Month3]),"Type","Product Group")
RETURN UNION(_GroupByProdGRP,_GroupByProdCD)

Then use the Type Field in the above table as Filter and remaining fields in the table visual.

[agoyal]

Hi @PattemManohar - thanks for your response on this. So the situation is that there are 12 months field which keeps on changing every month with new month coming in. With this appraoch i will have to update the formula every month. Do you know how come we overcome this situation.

[PattemManohar]

@agoyal Then in that case, you need to few more steps before creating a new table in DAX as below

In Power Query Editor, Unpivot all the columns other than the Month Fields using "Unpivot other Columns"

Then remaining steps are almost same as before, create a new table using DAX as below

Test233Out2 = 
VAR _GroupByProdCD = SUMMARIZE(Test233Grouping2,Test233Grouping2[Customer],Test233Grouping2[Month],Test233Grouping2[PROD_CD],"Value",SUM(Test233Grouping2[Value]),"Type","Product Code")
VAR _GroupByProdGRP = SUMMARIZE(Test233Grouping2,Test233Grouping2[Customer],Test233Grouping2[Month],Test233Grouping[PROD_GRP],"Value",SUM(Test233Grouping2[Value]),"Type","Product Group")
RETURN UNION(_GroupByProdGRP,_GroupByProdCD)

Now you need to use the Matrix visual instead of Table because we have data in row format and needs to be pivoted output. I've shown below both Matrix and Table visual outputs

Please Note - "Unpivot Other Columns" is the key here, if you add Month4 to your sample data (which I've tested already as shown above) it will automatically pickup without any changes to your code.