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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

rows as columns and columns as rows in report view

I have 4 tables as below and am unable to get output as countries in a column and all values from each table in rows.

Please help how to do this in report without changing tables in power query as the same tables are used for many other reports/views.

 

Data model.PNG

 

 

Budget Table:

CountryAmount B K$
India600
America850

 

Country: Table

Country
India
America

 

Sales Stage: table

 

CountryAmount SK$
India500
India400
India300
India250
America800
America500
America300
America150

 

Sales: table

CountryAmount S K$
India200
America100

 

Desired Output in powr bi report:

 

 IndiaAmerica
Budget Amt600850
Sales Stage Amt14501750
Sales Amt200100

 

When i tried Matrix to achive this its repeating all amount values for each country.

 

 

1 ACCEPTED SOLUTION

@Anonymous just realized you can do it without combining tables byt using "shows on rows" option in matrix, updated solution attached



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Wow.. nerver though it will this simple to do this :). Thank you so much 🙂

parry2k
Super User
Super User

@Anonymous best would be combine these 3 tables and then it is possible, see attached

 

You can combine thru power query (as shown in attached) or use DAX to combine tables.

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@Anonymous just realized you can do it without combining tables byt using "shows on rows" option in matrix, updated solution attached



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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