Data transform from row to column

[tonk]

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!

[Uzi2019 · Accepted solution]

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 Matrix 

This would give you exact same formatting that you are trying to achieved.

[Alex87 · Accepted solution]

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))+0

My 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

[Uzi2019 · Accepted solution]

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 Matrix 

This would give you exact same formatting that you are trying to achieved.