Category order is not as expected in waterfall

[GraceTCL]

Hello

I am trying to sort the waterfall category based on the order in the table [Inflow_Breakdown_standalone]. Unfortunately, the orders are not what I had expected even after sorting based on category based on the "..." more options in the top right hand corner of the waterfall chart visual.

This is how the waterfall chart looks like after sorting based on "category"

However, this is the other that I want as per [inflow_breakdown_standalone].[order]

Could you advise how can I get the order in the waterfall that I expect? Thanks!

[VijayP]

@GraceTCL 

Create a Index Column in the Data 

Then Sort the Category column by Index as shown below

And then in Waterfall Chart Sort by Category and Sort Ascending usibg "..." the most famous Three Dots.

you will be able to achieve what you require

[GraceTCL]

Hi @VijayP 

I am not sure where should I add the Index column. Currently I have added to 2 tables [Inflow_Breakdown_Standalone] and [Variance] where the 1st was intended for the categories of the waterfall and the other is the variance formula. 

While "index" appears as a drop down in "sort by colun", I still dont get the intended result.

Pls advise what did I do wrong? Thanks!

Index in Variance TableIndex in Inflow_Breakdown Standalone TableResult after sorting by Index

[v-kkf-msft · Accepted solution]

Hi @GraceTCL ,

The relationship between the sample tables I created is as follows:

First sort the Category column in Order Table:

1. Select the Category column.
2. Select the Order column in Sort by Column.

Then the Category field in the Order Table is placed in the category pane of the waterfall chart.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GraceTCL]

Hi @v-kkf-msft 

It works now! I dont know why it takes power BI one hour to reflect the updated model.

Previously I had a 1-1 relationship between the Order and Fact table but it didnt work and I update to a 1-many relationship from Order to Fact table and it works as per your advice. Could you clarify why is this the case?

Thank you!

[v-kkf-msft]

Hi @GraceTCL 

I use a one-to-one cardinality in my model, and the result is still correct. I guess it might be because the one-to-one relationship you created is not active.

For more information about the relationship between tables, please refer to  Create and manage relationships in Power BI Desktop 

Best Regards,
Winniz