cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
rauldip22 Frequent Visitor
Frequent Visitor

How to summarize and Transpose headers for rows?

Hi to everybody!

 

I have this problem:

 

I have this data:

 

CountrySellCost
Argentina3455675
USA3321346
Australia6754720
Argentina4332786
Canada7753986
Australia5522432
Argentina9856936
USA5436876
Spain3876946
Spain7634345
USA9468234
Argentina7643745

 

And I need to reshape table to obtain something like that:

 

 ArgentinaAustraliaCanadáSpainUSA
Sells252861227677531151018225
Costs3142115298612911456
Difference221441112467671021916769

 

The main idea is tu summarize the rows with few different measures (a lot) and transpose the summarized rows for use it as columns.

 

I started with the basic SUMMARIZE Formula in DAX: 

Sells Country = SUMMARIZE(Table1,Table1[Country],"Sells",SUM(Table1[Sell]))

But I dont know how to continue....

 

Could somebody help me?

 

Thanks!!

1 ACCEPTED SOLUTION

Accepted Solutions
dedelman_clng New Contributor
New Contributor

Re: How to summarize and Transpose headers for rows?

Hi @rauldip22 - you want to use a matrix visual instead of trying to recreate a table.  Country would be the columns, and your different measures for Sell, Cost, and Difference would be the values.  You'll also need to turn on "Show on Rows" under "Values" in the format painter.

Capture.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Hope this helps

David

 

 

 

 

 

 

3 REPLIES 3
dedelman_clng New Contributor
New Contributor

Re: How to summarize and Transpose headers for rows?

Hi @rauldip22 - you want to use a matrix visual instead of trying to recreate a table.  Country would be the columns, and your different measures for Sell, Cost, and Difference would be the values.  You'll also need to turn on "Show on Rows" under "Values" in the format painter.

Capture.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Hope this helps

David

 

 

 

 

 

 

rauldip22 Frequent Visitor
Frequent Visitor

Re: How to summarize and Transpose headers for rows?

Thanks!! It worked!

 

I have another question, there is a way to add aditional columns into the same Matrix? For example, If I Have "days" column like that:

 

CountryCategorySellsCost
ArgentinaA3455675
USAA3321346
AustraliaC6754720
ArgentinaB4332786
CanadaA7753986
AustraliaC5522432
ArgentinaB9856936
USAA5436876
SpainA3876946
SpainB7634345
USAC9468234
ArgentinaC7643745

 

I would like to add the Category column to the matrix and obtain something like this:

 ArgentinaAustraliaCanadáSpainUSAABC
Sells252861227677531151018225238412182229387
Costs3142115298612911456382929672131
Difference221441112467671021916769200121885527256

 

There is a way to develop this?

dedelman_clng New Contributor
New Contributor

Re: How to summarize and Transpose headers for rows?

I don't think that is possible in a single matrix.  You could rig up something where there are two matrix visuals and the one with Country overlaps the one with Category, but as soon as you get more countries or applied a slicer the whole thing would become a mess.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 27 members 945 guests
Please welcome our newest community members: