cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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!

2 ACCEPTED SOLUTIONS
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

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!!!
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``````

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.

Proud to be a Super User!

2 REPLIES 2
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``````

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.

Proud to be a 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

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!!!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors