Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
tonk
Frequent Visitor

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!

 

tonk_1-1716273419953.png

 

 

2 ACCEPTED SOLUTIONS
Uzi2019
Super User
Super User

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 

 

Uzi2019_0-1716277149293.png

 

 

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

 

 

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

View solution in original post

Alex87
Super User
Super User

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

Alex87_1-1716278188437.png

 

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.

 

Alex87_0-1716278152560.png

If it answers your query, please mark my reply as the solution. Thanks




Did I answer your question? Mark my post as a solution!
Appreciate your Like/Kudos

Proud to be a Super User!




View solution in original post

2 REPLIES 2
Alex87
Super User
Super User

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

Alex87_1-1716278188437.png

 

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.

 

Alex87_0-1716278152560.png

If it answers your query, please mark my reply as the solution. Thanks




Did I answer your question? Mark my post as a solution!
Appreciate your Like/Kudos

Proud to be a Super User!




Uzi2019
Super User
Super User

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 

 

Uzi2019_0-1716277149293.png

 

 

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

 

 

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.