Dynamic bar chart ignoring zero values

[redsox786]

Hello,

I'm trying to create a dynamic bar chart using the sample data attached.

Essentially what I'm trying to create is a dynamic bar chart that will:

1. X-Axis: For the month of January only, show Jack, Mike, and Steve and the names of the fruits.
2. Y-Axis: For the month of January only, show the number of fruits that correspond to each person.
3. If there is no value listed (in this case zero blueberries, don't show it on the chart (including the legend). But if I enter a blueberry value, dynamically add this to the bar chart for the respective user.
4. I'm then trying to create a slicer (or some type of selector) that will let me select the Month. So I'd like to see January as it's own bar chart, then once I select February, show the data for February only.

Any help would be HUGELY appreciated! I've been trying to make this work but have not been able to get it.

Attachment 574054

[teylyn]

Hello,

your data is laid out in a report format. This is really hard to use for the charts you want to create. You need to normalise your data into a flat table. Then you can use a pivot table to report on the data, grouping by the months, names or fruits as you desire.

I suggest you load the data into Get and Transform, select the first two columns and then issue the command to unpivot OTHER columns. This will give you a flat table that you can save in a worksheet.

Then build a pivot table with the data from that query.

If you get stuck, sing out.

cheers, teylyn

[redsox786]

I'm not too familiar on the Get and Transform function and creating Pivot Tables

Any chance you can help out by modifying the "Data.xlsx" sample worksheet?

[teylyn]

Here are the steps:

- select a cell in the data
- click Data > From Table/Range in the Get & Transform group
- tick the box "My Table has headers" in the dialog and click OK
- the Power Query Editor will open.
- select the two columns Month and Name
- in the Transform ribbon select the dropdown for Unpivot Columns and in there select "Unpivot Other columns"
- now you will see the columns Month, Name, Attribute and Value
- rename Attribute to "Fruit" and rename Value as you see fit or leave it as Value
- In the right hand side panel rename the query from "Table1" to something more descriptive
- On the home ribbon, click Close and Load
- the four columns of data will now be loaded into a new sheet
- click any cell in the new table and click Insert > Pivot Chart
- drag the Name and the Fruit fields into the Axis (Categories) area and the Value field into the Values area
- insert a slicer for Month

See attached file.

[redsox786]

Thank you SO much Teylyn! This is a huge help.

Just one follow up: Is it possible to exclude fruits from being plotted on the graph if the value is "0"? For example, Blueberries are zero across the board so I'm trying to avoid plotting them. But if I do decide to enter a value for blueberries for a user, can it automatically be plotted?

[teylyn]

You can add a step in the Power Query to remove rows with 0 values. Then they won't show up in the pivot chart.

[markbarker2000]

Hello. Would this work for you? I have created a pivot chart which shows what you need.

[markbarker2000]

Look at B1 and this should allow you to filter the month to show the chart you need