Calculated column based on condition depending on other columns

---

Anonymous wrote:

amitchandak thanks, but as I pointed out in the post the question is not about creating the column. I'm wondering if there's a way to get the calculated column based on last two columns before it, without having to hardcode column names.

---

Not in DAX. You would have to do this in Power Query or normalize your data in Power Query before bringing into DAX. 
Here is what I did. This is not the answer you are looking for, but may get you down that path.

First, I normalized your data. So 

becomes:

The [Index] column will tell me what the last 2 columns are as I cannot rely on the column names, now the [Category] column to reliably tell me the most recent two based on whatever text string is in there.

M code for that is:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs3LLFHSUXJOLElNzy+qdERiOyGxnZVidYCKDYFClpYgwgBIWBhBRI1AAiDC0AAkbGoGETYGCZuBBECiZlBRE5BGUyBhbAwxDCwKEjACm24CNSc2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"CategoryA", Int64.Type}, {"CategoryB", Int64.Type}, {"CategoryC", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Unit"}, "Category", "Amount"),
    #"Grouped Rows" = 
        Table.Group(
            #"Unpivoted Other Columns", 
            {"Unit"}, 
            {
                {"All Rows", each Table.AddIndexColumn(_, "Index", 1, 1), type table [Unit=nullable text, Category=text, Amount=Int64.Type, Index=Int64.Type]}
            }
        ),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"Category", "Amount", "Index"}, {"Category", "Amount", "Index"})
in
    #"Expanded All Rows"

Then, I used this measure.

Measure = 
VAR varMaxIndex =
    CALCULATE(
        MAX( 'Table'[Index] ),
        ALLEXCEPT(
            'Table',
            'Table'[Unit]
        )
    )
VAR varMaxCategory =
    CALCULATE(
        MAX( 'Table'[Category] ),
        'Table'[Index] = varMaxIndex,
        ALLEXCEPT(
            'Table',
            'Table'[Unit]
        )
    )
VAR var2ndMaxCategory =
    CALCULATE(
        MAX( 'Table'[Category] ),
        'Table'[Index] = varMaxIndex - 1,
        ALLEXCEPT(
            'Table',
            'Table'[Unit]
        )
    )
VAR Result =
    CALCULATE(
        SUM( 'Table'[Amount] ),
        FILTER(
            ALLEXCEPT('Table', 'Table'[Unit]),
            'Table'[Category]
                IN {
                varMaxCategory,
                var2ndMaxCategory
            }
        )
    )
RETURN
    Result

This will always return the highest and 2nd highest [Index] per unit, and consequetly the last two categories.

You can see this in this table visual.

The value for U1 is 172, which is 90 + 82. U2 is 156, which is 100 + 56.

This measure works anywhere, so it could be a bar chart with none of the other values in it, like so:

Does that help? If not, provide some feedback so we can continue to assist.