Toggle columns to be group by

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

Need Help

Show and Tell

Tips and Tricks

v-yulgu-msft
7 years ago
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

4 Replies

PattemManohar
Community Champion
7 years ago
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
  Frequent Visitor
  7 years ago
  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
    Community Champion
    7 years ago
    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.
v-yulgu-msft
Microsoft Employee
7 years ago
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