Forum Discussion
Chart Sorting - Highest to Lowest
This is a 3-fold question ...
Using the test-data below as to which adults and kids like the various fruit (1 represents they like that fruit), my goal is to have a chart that shows which fruit is the most popular compared on down to which one is the least popular.
I can pull the data into a Power BI report, but am not finding how to do the sort from most popular to least popular.
1) My data comes from a website questionnaire and is downloaded into Excel similar to this. My goal will be to keep uploading (or auto refreshing) the new data received so the charts will continously reflect the additional/new data received.
| Version | First Name | Last Name | Location | Oranges | Apples | Watermelon | Pineapple | Bananas | Strawberries |
| Kids | FN1 | LN1 | Location1 | 1 | 1 | 1 | 1 | ||
| Adults | FN2 | LN2 | Location2 | 1 | 1 | 1 | |||
| Adults | FN3 | LN3 | Location1 | 1 | 1 | ||||
| Kids | FN4 | LN4 | Location2 | 1 | 1 | 1 | 1 | 1 | 1 |
This is what my data looks like thus far.
2nd Item: Need help getting the names of the fruit the bars in the chart represent underneath each bar rather than represented by a different color and noted in the Legend Name. Note: My real data has about 50 different options that will be sorted.
3rd Item: I'll also need to be able to filter by Version (Kid, Adult or both) and Location (Location1, Location 2, or both)
Thank you for your assistance!
- I think all three of these items can be solved if you UNPIVOT your fruits. https://radacad.com/pivot-and-unpivot-with-power-bi
Scroll down to the Unpivot section in that post.
Since you have over 50 options, I would recommend you select the demographic columns (Version, Location, etc) and then UNPIVOT OTHER COLUMNS. but up to you which is easier.
Once you have done the Unpivot in Power Query, it will be easy to put the 'Attribute' (which you will probably rename to 'Fruit' or something similar) in Axis and 'Value' in Values to get the chart you want.
You can also then easily sort by most popular fruit/attribute/item https://docs.microsoft.com/en-us/power-bi/consumer/end-user-change-sort Hi tgreve4ts ,
as AllisonKennedy mentioned you must first unpivot your data (fruit columns) with Power Query. Then you can build very easy the report like this:
Regards FrankAT
4 Replies
- I think all three of these items can be solved if you UNPIVOT your fruits. https://radacad.com/pivot-and-unpivot-with-power-bi
Scroll down to the Unpivot section in that post.
Since you have over 50 options, I would recommend you select the demographic columns (Version, Location, etc) and then UNPIVOT OTHER COLUMNS. but up to you which is easier.
Once you have done the Unpivot in Power Query, it will be easy to put the 'Attribute' (which you will probably rename to 'Fruit' or something similar) in Axis and 'Value' in Values to get the chart you want.
You can also then easily sort by most popular fruit/attribute/item https://docs.microsoft.com/en-us/power-bi/consumer/end-user-change-sortAllisonKennedy Huge Virtual High-5! Your assistance was so appreciated! Everything is working/sorting as I needed it to now. Thank you!
Hi tgreve4ts ,
as AllisonKennedy mentioned you must first unpivot your data (fruit columns) with Power Query. Then you can build very easy the report like this:
Regards FrankAT
