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

[DirkDragon]

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:

And here's what I want BI to show:

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?

[mahenkj2]

Hi @DirkDragon 

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

[mahenkj2]

Hi @DirkDragon 

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

Hope it helps.

[DirkDragon]

Sample Data Here's the data set. 

[mahenkj2 · 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).

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

[DirkDragon]

Thank you! Very helpful.