Forum Discussion
Data transform from row to column
Hi all,
May I know how to transform from the following input to the following output in Power? I tried to apply pivot. However I can only select one column either qty or amt. Thanks!
Hi tonk
Juts use matrix visual .
use customer and model in a Row section of a Matrix
Use Month in a column section of a Matrix visual
then use Sales qty and sales amt in Values section of a MatrixThis would give you exact same formatting that you are trying to achieved.
In order to have this exact output, you need to use a table with Customer & Model and then add DAX measures for each month.
Oct-23 Sales Qty = CALCULATE( SUM(YourTable[Sales Qty]), FILTER( YourTable, MONTH(YourTable[Month]) = 10))+0 Oct-23 Sales amt = CALCULATE( SUM(YourTable[Sames amt]), FILTER( YourTable, MONTH(YourTable[Month]) = 10))+0 Nov-23 Sales Qty = CALCULATE( SUM(YourTable[Sales Qty]), FILTER( YourTable, MONTH(YourTable[Month]) = 11))+0 Nov-23 Sales amt = CALCULATE( SUM(YourTable[Sames amt]), FILTER( YourTable, MONTH(YourTable[Month]) = 11))+0 Dec-23 Sales Qty = CALCULATE( SUM(YourTable[Sales Qty]), FILTER( YourTable, MONTH(YourTable[Month]) = 12))+0 Dec-23 Sales amt = CALCULATE( SUM(YourTable[Sames amt]), FILTER( YourTable, MONTH(YourTable[Month]) = 12))+0My recommendation is to avoid this output as it is not scalable and not visually clear.
I would opt for something more dynamic and clear like
Using a matrix with Customer & Model on rows, Month on column and a simple sum measure for Qty & Sales Amount on Values
I would activate in the format pane Values/Options/ Switch values to rows ON. The output is scalable, easier to maintain and clear.
If it answers your query, please mark my reply as the solution. Thanks
2 Replies
- Alex87Solution Sage
In order to have this exact output, you need to use a table with Customer & Model and then add DAX measures for each month.
Oct-23 Sales Qty = CALCULATE( SUM(YourTable[Sales Qty]), FILTER( YourTable, MONTH(YourTable[Month]) = 10))+0 Oct-23 Sales amt = CALCULATE( SUM(YourTable[Sames amt]), FILTER( YourTable, MONTH(YourTable[Month]) = 10))+0 Nov-23 Sales Qty = CALCULATE( SUM(YourTable[Sales Qty]), FILTER( YourTable, MONTH(YourTable[Month]) = 11))+0 Nov-23 Sales amt = CALCULATE( SUM(YourTable[Sames amt]), FILTER( YourTable, MONTH(YourTable[Month]) = 11))+0 Dec-23 Sales Qty = CALCULATE( SUM(YourTable[Sales Qty]), FILTER( YourTable, MONTH(YourTable[Month]) = 12))+0 Dec-23 Sales amt = CALCULATE( SUM(YourTable[Sames amt]), FILTER( YourTable, MONTH(YourTable[Month]) = 12))+0My recommendation is to avoid this output as it is not scalable and not visually clear.
I would opt for something more dynamic and clear like
Using a matrix with Customer & Model on rows, Month on column and a simple sum measure for Qty & Sales Amount on Values
I would activate in the format pane Values/Options/ Switch values to rows ON. The output is scalable, easier to maintain and clear.
If it answers your query, please mark my reply as the solution. Thanks