Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
11 months ago

Sort Axis on Waterfall Chart

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 M...
GeraldGEmerick's avatar
GeraldGEmerick
Super User
11 months ago

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's avatar
Syndicate_Admin
Administrator
11 months ago

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's avatar
    GeraldGEmerick
    Super User
    11 months ago

    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.