Recreating Excel Graph In Power BI Proving Difficult

[Broncko]

Hello, Very new to Power BI and this community so appologies in advance if this is elementary.

Currently challenged with switching our reporting suite from excel to Power BI. I have what is a relatively easy column chart in Excel that is proving difficult in BI. Can anybody tell me if this is even possible in Power BI?

Excel Chart at top Power BI on bottom:

In my head the solution would be to put the 'Bar/shop' etc on the x axis and the 'year' in the legend but Power Bi won't allow it. This approach can only be done with one 'value'Excel ChartPower BI Graph

[v-juanli-msft]

Hi @Broncko 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case and help the other members find it more quickly?

If not, please share some data example for further analysis.

Best Regards

Maggie

[vivran22]

Hello @Broncko 

It depends on how you have structured your data. But going by the description, you can get the desired results when you have all the key metric (Staff assistance, Cleanliness, etc) in one column and their respective scores in another column. Not to mention, year/date should be in a separate column. Then you can create a visual like this:

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

[Broncko]

Hello Vivek, 

Thank you for responding. Much appreciated.

That's absolutely the solution i aspire to get to. However, i'm still not sure of the methodology to get there.

So i have a raw data extract for how each individual survey scored all the categories 1-10 (Bar,Cleanliness etc) and the year/date of the survey. (I'm looking to use an average of the thousands of survey scores). It feeds into Power BI from an Excel table and is structured as in the attached image. The data looks exactly the same in Power BI (I just couldn't remove all the sensitive stuff quickly enough to send a screen shot)

How do i need to restructure this? Can it be done within Power BI itself or prior to the import?

[vivran22 · Accepted solution]

@Broncko 

Go to Power Query (Transform Data) 

Select all the columns (1-10) which you want to transform

Select Unpivot Columns

Hope this should help.

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

[Broncko]

Amazing! Thanks Vivek. never would have figured that out

I had to duplicate the data table/query in Power Query and then unpivot - Otherwise the duplicates skewed some other reports/visuals i had feeding from the original table.

That's not really inefficent bad prcatice is it? Duplicating the table/query - 🤔

[vivran22]

@Broncko 

I am glad the solution worked for you.

Instead of Duplicate, I would recommend you should use Reference. This way, any changes to the orignal/source table will be reflected in the dependent tables.

And in terms of efficiency, it is very subjective and depends on the table size and data model. But it works well in most of the cases.

Cheers!
Vivek

https://www.vivran.in/
Connect on LinkedIn