Problem with sorting years in Waterfall Chart

[gserbezov]

Hello everyone, 

I have a problem with the waterfall chart. I would like to visualize a sales variance by year in a waterfall chart, but when I put the Year in the category field, on the visualization the years are not sorted. When I choose some other visualization like bar chart or area chart or whatever, they are sorted. The Year field is in a Whole Number format, sorted by Year(default), so I really have no idea what is the problem. Does somebody know any solution or have some idea how to deal with this.. 

 The years are not sortedThe Year field seems to be fine

[Omosaad]

You can sort the values on the x-axis of the waterfall chart by hitting the elipsis at the top right corner (more options) and from their you can select the year to sort by and select sort ascending, that way the waterfall chart will be showing the right way with ascendingly sorted values on the x-axis.

[Anonymous]

I had a work around to be able to resolve the issue

1. Go to Data View
2. Select the Date database
3. Right Click on the Header and click on [New Column]
4. I named the column as follows: YearNew = 'Date'[Date].[Date]
5. Then in Modeling select Data Type as : Date
6. In the same group select Format > Date Time > 2001 (yyyy)
7. Go back to your report
8. Use the YearNew as the Category

[Anonymous]

I had a work around to be able to resolve the issue

1. Go to Data View
2. Select the Date database
3. Right Click on the Header and click on [New Column]
4. I named the column as follows: YearNew = 'Date'[Date].[Date]
5. Then in Modeling select Data Type as : Date
6. In the same group select Format > Date Time > 2001 (yyyy)
7. Go back to your report
8. Use the YearNew as the Category

[klabir]

Hello

We created the Power BI Ultimate Waterfall Custom Visual.

It has a lot of clever functionality built-in. Like Chart orientation, small multiples, sub totals, Start value, deviation calculation and many more.

http://dataviz.boutique/2017/03/22/powerbi-ultimate-waterfall-custom-visual/

We are interested in your feedback!

Which functionality you still miss?

THX

Klaus

[Anonymous]

I am not sure if this bug report is being followed but I am able to reproduce the bug. If the year is dragged onto the canvas first (or, added to a waterfall template), everything works fine. However, if the Y-Axis is added/dropped first, the order of the years is all mixed up. Well, not really mixed up but it is sorted from highest to lowest value based on Y-Axis value.

There is also a community thread that is discussing this issue. 

[dmgutenkauf]

I had this issue today. It seems to be a case where Power BI gets "stuck" somehow. While trying to fix it, I changed the chart to a table, resorted by the category column (even though the table was already sorted correctly), and when I changed back to the waterfall the sort was fixed.

Now that it's fixed, I can no longer recreate the initial issue... go figure.

Hope this works for you too, or anyone else who stumbles on this!

[PetaG]

Hi, same here - my years go from current on the left to 2000 on right hand side.(back to front)

I tried your workaround (created table & matrix, then back again) to no avail.

I also tried someone elses suggestion & recreated, making sure year field was placed last - no good.

Has anyone in the community got any further suggestions.

thanks

[bazicalos]

Faced the same problem. Handled it with the help of Microsoft Docs article.

In short here are the steps:

1. Call chart menu pressing [...] button on the top right of the chart

2. Proceed to "Sort by" item

3. In submenu press "Year" item. The order should be correct now

4. If the Y-axis is shown upside-down, go to the same menu and press "Sort accending"

This worked for me.

[PetaG]

I've had the same problem. Tried your fix (created table & Matrix & sorted on year), but with no luck when I went back to the waterfall.

[elenaterenzi]

Same problem here.

I believe that, despite suggested workarounds, we have a bug here:

if the chart has a sortable axis, then that should be by default the sorting priority

in this case the waterfall chart is sorting by variance instead of giving precedence to axis order.

this might be a nice to have feature (sorting by the variance measure) but not the default behavior imho.

[ankitpatira]

@gserbezov what you need to do is go to Data view and select your table. Select Y Axis field of waterfall chart and click on Sort By Column dropdown. Then select Category field of waterfall chart (which will be Year in your case). This will sort sales variance by year.

[gserbezov]

@ankitpatira I tried your way, but still not working. The Sales variance is a calculated measure and it can not be sorted, so I tried with the Revenue field, which is in the same table and it`s used for calculating the Sales Var, but then the Year(Category field) is in another table and it is not in the sorting drop down for Revenue, so I checked the relationship and the Date table is connected with the Sales table by the Date field. I tried to sort the Revenue by Date field but then an error message pops up and says that it`s not possible. 

DateSales VarianceError message when try to sort Revenue by Date

[Eric_Zhang]

@gserbezov

Instead of measures, can you try to use a calculated table? The sorting works in the calculated table.

If you have any question, feel free to let me know.