Sort Axis on Waterfall Chart

[Syndicate_Admin]

I have a consultation

I've generated a waterfall chart in Power BI with the variables
CATEGORY: Mg 2025P & Mg 2025R
BREAKDOWN: Mg Serv, Mg Eq, C. Op and C. Com
Y-axis: Sum of Total



My chart works perfect but I'd like to sort my X-Axis, i.e. where CATEGORY and BREAKDOWN go. This, because every time I filter by month, it keeps CATEGORY sorted, but it sorts me BREAKDOWN by value and not by name.

I even rename the variables with a number before, but it keeps getting messy. There is some way to order it, I have tried:
- Creating tables with order, but they don't work because each variable has more than one match.
- Creating order measures within the same table where the waterfall variables are, but I generated a redundancy error.
- Renaming in Power Query (to put a number before, which it shouldn't be), but it didn't work

If someone can guide me if it is possible to do what I need, and if it is, it does not complicate me if I should make it with DAX measurement or code in Power Query. Of course, thanks to any possible help.

[v-nmadadi-msft]

Hi @Syndicate_Admin ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[Syndicate_Admin]

You explain to me how the structure of that table I must create should be. I guess I should do this in Power Query, to indicate the order of this column (Breakdown) and keeping the order of Category

[v-nmadadi-msft]

Hi @Syndicate_Admin ,

You can build this mapping table in Power Query (manually create a small table with those pairs). Once you have it:

1. Load it into the model.

2. If it’s a separate mapping table, create a relationship from BREAKDOWN in your fact table to BREAKDOWN in this mapping table, then add the SortOrder column to your fact table via a merge.

3. In the Data view, select your BREAKDOWN column, then set Column Tools → Sort by Column → SortOrder.

This way, whenever you use BREAKDOWN in the Waterfall chart, it will always respect the custom order you defined, regardless of which CATEGORY or filter you apply.

I hope this information helps. Please do let us know if you have any further queries.
Thank you

[Syndicate_Admin]

I'm trying to do the sorting by modifying the M code in Power Query but I'm not getting to the expected result. Someone who knows whether or not the BREAKDOWN variables can be sorted in a waterfall chart and that they remain fixed, regardless of the filters applied

[GeraldGEmerick]

@Syndicate_Admin It's difficult to be certain without full sample data to test but I believe what you would want to do in your case is to have a Sort By column for the CATEGORY column. This should be a numeric column that you would add in Power Query and there would be a 1:1 relationship between unique values in the CATEGORY column and this Sort By column. You should then be able to use the ellipses (...) in the Waterfall chart to change the sort axis to CATEGORY and then either ascending or descending as appropriate.

[Syndicate_Admin]

For the data, you need a PBIX file or what numbers they have, according to the months I am filtering???

I had thought of something like that, looking for how to generate an order column in Power Query to sort, in my case BREAKDOWN (because CATEGORIES is correctly ordered), you guide me with my doubt.

[GeraldGEmerick]

@Syndicate_Admin Well, I think that the column would look something like the following. PQ only supports if then else statements so it is a bit messy but for a limited number of categories it should work. This would be entered into an Add Custom Column formula (without the = sign):

=
if [BREAKDOWN] = "Mg Serv" then 1 else 
if [BREAKDOWN] = "Mg Eq" then 2 else 
if [BREAKDOWN] = "C. Op" then 3 else
if [BREAKDOWN] = "C. Com" then 4 else
5

[Syndicate_Admin]

It's in my Categories column (created in Power Query)
if [Year] = 2025 and List.Contains(
{"A. Service Income", "B. Service Cost D", "C. Electricity", "D. Equipment Revenue", "E. Equipment Cost", "F. Commercial Cost", "G. Serti Operational Cost"},
[Line Template]
)
then
if [Income Type] = "PLAN" then "Mg Contribution 2025P"
else if = "REAL" and [Month] <= 6 then "Mg Actual Contribution"
else if = "OL" and [Month] >= 7 then "Mg Real Contribution"
else null
else null

This is my Breakdown column (created in Power Query)
if [Year] = 2025 then
Mg Services
if List.Contains({"01. Income Services","02. Costs of Services"}, [Line Cost N1]) then
if [Income Type] = "PLAN" then "2. δ mg services"
else if = "REAL" and [Month] <= 6 then "2. δ mg services"
else if = "OL" and [Month] >= 7 then "2. δ mg services"
else null
Mg Equipment
else if List.Contains({"04. Income from the sale of equipment/products","05. Equipment Costs"}, [Line Cost N1]) then
if [Income Type] = "PLAN" then "3. δ mg equipment"
else if = "REAL" and [Month] <= 6 then "3. δ mg equipment"
else if = "OL" and [Month] >= 7 then "3. δ mg equipment"
else null
// C. Oper. VP Digital
else if [Line Template] = "F. Trade Cost" then
if [Income Type] = "PLAN" then "4. Δ C. Op. VP Digital"
else if = "REAL" and [Month] <= 6 then "4. Δ C. Op. VP Digital"
else if = "OL" and [Month] >= 7 then "4. Δ C. Op. VP Digital"
else null
C. Commercial
else if [Linea Template] = "G. Cost-Operational Serti" then
if [Income Type] = "PLAN" then "5. Δ C. Commercial"
else if = "REAL" and [Month] <= 6 then "5. Δ C. Commercial"
else if = "OL" and [Month] >= 7 then "5. Δ C. Commercial"
else null
else null
else null

This is my measure Amount (created with DAX)

03. MONTO_CASCADA =

VAR MesHoy = MONTH(TODAY()) - 2

VAR MonthFiltered = VALUES(Base_Margen[Mes])

VAR CantMonthsFiltered = COUNTROWS(MonthFiltered)

VAR HayFiltroMes = ISFILTERED('Dim_Calendario'[MesCorto])

VAR MonthSelected =

IF(

HayFiltroMes,

MAX('Dim_Calendario'[MesNum]),

MesHoy

)

VAR TypeIncome = IF(MonthSelected <= 6, "REAL", "BE")

RETURN

CALCULATE(

SUM('Base_Margen'[Amount]),

FILTER(

ALL('Dim_Calendario'[MesNum]),

'Dim_Calendario'[MesNum] <= MonthSelected

)

)

The values are correct and square, and the Category column is sorted correctly, the problem I have is with the Breakdown column. You guide me how to implement an order that is maintained, regardless of the months or products that I filter

[GeraldGEmerick]

@Syndicate_Admin Right, so then you need to create another Breakdown Sort column that references each unique value in your Breakdown column and assigns a number to it.