Creating a waterfall not using dates and resetting middle of graph to zero

[Tommy123]

here is a dummy waterfall. What I want is between Discount 2 and intermediate to reset at 0
so the 40 would start at from 0. 
I tried adding another row that is -40 but that wont work. I am wondering if anyone has any idea on what I can do ? 
Catergory          Value   Order

Starting Price	50	1
Discount 1	-4	2
Discount 2	-6	3
intermediat	40	4
final discount 1	-10	5
final discount 2	-10	6
ending price	20	7

here is sort of what I want it ot look like. Its not my data but how I want it to look

[Tommy123]

I appreciate the help. I ended up creating 2 graphs seperatly and overlaying them on one another. 

[Tommy123]

Exactly. I just dont need the reset part of the graph, that is what I dont need. I tried that as well

[v-jtian-msft]

Hi,@Tommy123 .Thank you for your reply.
Unfortunately, the waterfall chart automatically displays the data in aggregated categories based on the fields you have placed.
If you want to zero out the data in the waterfall chart, you need to add the opposite data so that the previous aggregated value = 0. Otherwise, the subsequent data will always be displayed on top of the original aggregated value (instead of displaying the data from zero).
The last column, Aggregate, is the final scalar value of your previous measure calculations, so the final total will take into account the value of the Reset column, which is only used to zero out the data.

I tried to manipulate the data in the total column using isincope to control it separately (this works in matrices and tables), but unfortunately. In the waterfall chart the total column is also attributed to the filter of the field

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-jtian-msft · Accepted solution]

Hi,@Tommy123 .I am glad to help you.
Assuming your real data is as follows
 

You want to add a row of reset data to zero out the data in the waterfall chart.

The effect is shown in the waterfall visual:

If you only need to ensure that the presentation effect changes in the waterfall diagram without affecting the rest of the visual.
Creating a new calculation table using DAX is a good option

My Dax Code:

Use the UNION function to achieve data splicing (the number of column fields and column names are the same)
Use the RANKX function to generate a ranking sequence (the last parameter is changed from DENSE to SKIP to ensure that the same rank is not displayed for the same data)

AdjustedTableDAX = 
VAR OriginalTable = 
    ADDCOLUMNS(
        'waterfallTest',
        "CumulativeValue", 
        SUMX(
            FILTER(
                'waterfallTest',
                'waterfallTest'[Order] <= EARLIER('waterfallTest'[Order])
            ),
            'waterfallTest'[Value]
        )
    )
VAR ResetRow = 
    SELECTCOLUMNS(
        FILTER(
            OriginalTable,
            'waterfallTest'[Catergory] = "Discount 2"
        ),
        "Catergory", "Reset",
        "Value", -[CumulativeValue],
        "Order", [Order] + 1,
        "CumulativeValue", BLANK()  -- Ensure the same number of columns
    )
VAR AdjustedTable = 
    UNION(
        SELECTCOLUMNS(
            FILTER(
                OriginalTable,
                'waterfallTest'[Order] <= 3
            ),
            "Catergory", [Catergory],
            "Value", [Value],
            "Order", [Order],
            "CumulativeValue", [CumulativeValue]
        ),
        ResetRow,
        SELECTCOLUMNS(
            FILTER(
                OriginalTable,
                'waterfallTest'[Order] > 3
            ),
            "Catergory", [Catergory],
            "Value", [Value],
            "Order", [Order] + 1,  -- Adjust the order for subsequent rows
            "CumulativeValue", [CumulativeValue]
        )
    )
RETURN
    SELECTCOLUMNS(
        ADDCOLUMNS(
            AdjustedTable,
            "NewOrder", RANKX(AdjustedTable, [Order], , ASC, DENSE)
        ),
        "Catergory", [Catergory],
        "Value", [Value],
        "Order", [NewOrder]
    )

If you want to do additional calculations (create new measure/calculate columns) while modifying the format of the table
It is also a good idea to use M code to create a new table.

This is my M code:

let
    Source = Excel.Workbook(File.Contents("C:\Users\username\Desktop\test_1_14.xlsx"), null, true),
    waterfallTest_Sheet = Source{[Item="waterfallTest",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(waterfallTest_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Catergory", type text}, {"Value", Int64.Type}, {"Order", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Cumulative" = Table.AddColumn(#"Added Index", "CumulativeValue", each List.Sum(List.FirstN(#"Added Index"[Value], [Index]))),
    #"Reset Row" = #table(
        {"Catergory", "Value", "Order", "CumulativeValue", "Index"},
        {{"Reset", -#"Added Cumulative"{2}[CumulativeValue], 4, -#"Added Cumulative"{2}[CumulativeValue], 4}}
    ),
    #"Inserted Reset" = Table.Combine({Table.FirstN(#"Added Cumulative", 3), #"Reset Row", Table.Skip(#"Added Cumulative", 3)}),
    #"Removed Existing Cumulative" = Table.RemoveColumns(#"Inserted Reset",{"CumulativeValue", "Order", "Index"}),
    #"Added Index1" = Table.AddIndexColumn(#"Removed Existing Cumulative", "Order", 1, 1, Int64.Type),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Index1",{{"Value", type number}})
in
    #"Changed Type1"

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.