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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
DirkDragon
New Member

How can I have certain columns be my X axis with the values in those columns be my Y axis?

I have a data set where columns H-P are screens that measure the % of the sample that were retained on that screen. What I want to do is have the column headings in H-P be the X axis and the values underneath it be the Y axis. I want to be able to take the average of each column with whatever timeframe I select to give me a distribution of the product. 

 

Here's what the data looks like:

DirkDragon_0-1687271575917.png

 

And here's what I want BI to show:

DirkDragon_1-1687271630727.png

I was only able to make that graph by pivoting the data to give me the averages and on a seperate sheet in excel I made the table seen above. 

 

Is there a way to do this without reformatting the entire data set?

1 ACCEPTED SOLUTION

Hi @DirkDragon 

 

The data format you shared in Excel is 'wide' format. I believe 'long' data format is more appropriate for data analysis and visual representation purposes.

 

Also, Power BI have power query and DAX which can calculate the things on the way. So your base tables should have raw data and preferably not the already calculated matrices (if possible).

 

I attach a .pbix file using Power query to transform the data, advantage is that making desired visual is right away possible and you still you can generate the same table view even with this transformed data (matrix visual).

 

mahenkj2_0-1687348370452.png

https://drive.google.com/file/d/1u7sxJYXbk3BRSLQWkeYtGoNTuTNCC4bt/view?usp=sharing

 

Hope it helps.

 

https://community.fabric.microsoft.com/t5/Desktop/Equivalent-behavior-to-Switch-Rows-Columns-in-Exce...

 

View solution in original post

5 REPLIES 5
mahenkj2
Solution Sage
Solution Sage

Hi @DirkDragon 

 

You may consider to accept it as a solution if it works for you.

mahenkj2
Solution Sage
Solution Sage

Hi @DirkDragon 

 

Can you please share the excel file here if such data is not sensitive.

 

Hope it helps.

Sample Data Here's the data set. 

Hi @DirkDragon 

 

The data format you shared in Excel is 'wide' format. I believe 'long' data format is more appropriate for data analysis and visual representation purposes.

 

Also, Power BI have power query and DAX which can calculate the things on the way. So your base tables should have raw data and preferably not the already calculated matrices (if possible).

 

I attach a .pbix file using Power query to transform the data, advantage is that making desired visual is right away possible and you still you can generate the same table view even with this transformed data (matrix visual).

 

mahenkj2_0-1687348370452.png

https://drive.google.com/file/d/1u7sxJYXbk3BRSLQWkeYtGoNTuTNCC4bt/view?usp=sharing

 

Hope it helps.

 

https://community.fabric.microsoft.com/t5/Desktop/Equivalent-behavior-to-Switch-Rows-Columns-in-Exce...

 

Thank you! Very helpful. 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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