My Post was flagged as Spam?

Hello All, I have a Matrix visual I need help with. I have some sample data, Matrix Visual showing drill down rows, my current M Query for the Display table that this visual has and then the current measures I need help with. 

I have 3 custom columns:

1. Sub Category Sales
2. Sub Category Display Sales
3. 1 Shr Point of Display $ Opp

Then my 4 Dax Measures

1. Dollar Share of Subcategory (m) =
2. Dollar Share of Subcategory Display Only (m) =
3. 1 Shr Pt of Display $ Opp =
4. Display $ OPP =

Expected Results for the Product Row (lowest level) for the item with $347,281 in Total Sales (Dollar Sales column):

Dollar Share of Subcategory (m) = 3.15 (I have this column in my raw data)

Dollar Share of Subcategory Display Only (m) = 4.85 (I have this column in my raw data)

1 Shr Pt of Display $ Opp = $3472.81 ($347,281 in Dollar Sales time .01)

Display $ OPP = If (Dollar Share of Subcategory Display Only (m) > 0, 0) ELSE (Dollar Share of Subcategory Display Only (m) - Dollar Share of Subcategory (m)) * 1 Shr Pt of Display $ Opp = 0

So in this example I should have zero since Dollar Share of Subcategory Display Only (m) is greater than Dollar Share of Subcategory (m)

So the row with $30,234, which has an opportunity:

Dollar Share of Subcategory (m) = .09 

Dollar Share of Subcategory Display Only (m) = 0.0

1 Shr Pt of Display $ Opp = $302.34 ($30,234 in Dollar Sales time .01)

Display $ OPP = If (Dollar Share of Subcategory Display Only (m) > 0, 0) ELSE (Dollar Share of Subcategory Display Only (m) - Dollar Share of Subcategory (m)) * 1 Shr Pt of Display $ Opp = $27.21

After spending 8 hours between ChatGPT and MS Co-Pilot I am giving up on them and hoping for help here.

let
    // === STEP 1: Load the Excel workbook ===
    Source =
        Excel.Workbook(
            File.Contents("C:\Users\IW5S01\OneDrive - Flowers Foods, Inc\Documents\_Data Providers\Circana\PBI Data Files - Circana\Display\Display Scorecard.xlsx"),
            null,
            true
        ),

    // === STEP 2: Read the Index tab to get tab names and time text ===
    IndexSheet = Source{[Item = "Index", Kind = "Sheet"]}[Data],
    IndexNoHeaders = Table.Skip(IndexSheet, 1),

    RawTabNames = List.FirstN(List.Skip(Table.Column(IndexNoHeaders, "Column1"), 0), 12),
    RawTimeLabels = List.FirstN(List.Skip(Table.Column(IndexNoHeaders, "Column3"), 0), 12),

    TabNames =
        List.RemoveNulls(
            List.Select(
                List.Transform(RawTabNames, each Text.Trim(Text.From(_))),
                each _ <> "" and _ <> "Index"
            )
        ),
    TimeLabels =
        List.RemoveNulls(
            List.Select(
                List.Transform(RawTimeLabels, each Text.Trim(Text.From(_))),
                each _ <> ""
            )
        ),

    IndexMap = Table.FromColumns({ TabNames, TimeLabels }, { "TabName", "Time" }),

    // === STEP 3: Function to read & clean each report tab ===
    GetSheet = (name as text) =>
        let
            SheetData = Source{[Item = name, Kind = "Sheet"]}[Data],
            RemovedTopRows = Table.Skip(SheetData, 7),
            PromotedHeaders = Table.PromoteHeaders(RemovedTopRows, [PromoteAllScalars = true]),
            ChangedType =
                Table.TransformColumnTypes(
                    PromotedHeaders,
                    {
                        { "Geography", type text },
                        { "Product", type text },
                        { "Custom Subcategory Name", type text },
                        { "Custom Parent Name", type text },
                        { "Custom Mega Brand Name", type text },
                        { "Dollar Sales", type number },
                        { "Dollar Sales Display Only", type number },
                        { "Dollar Share of SubCategory", type number },
                        { "Dollar Share of SubCategory Display Only", type number },
                        { "Dollar Share of Category", type number },
                        { "Dollar Share of Category Display Only", type number }
                    }
                ),
            RenamedColumns =
                Table.RenameColumns(
                    ChangedType,
                    {
                        { "Custom Mega Brand Name", "Brand" },
                        { "Custom Parent Name", "MFG" },
                        { "Custom Subcategory Name", "Sub Category" }
                    }
                ),
            UppercasedGeo = Table.TransformColumns(RenamedColumns, { { "Geography", Text.Upper, type text } }),
            ReplacedGeo1 =
                Table.ReplaceValue(UppercasedGeo, "ALBERTSONSCO ", "ACI ", Replacer.ReplaceText, { "Geography" }),
            ReplacedGeo2 =
                Table.ReplaceValue(ReplacedGeo1, "-RMA - FOOD", "", Replacer.ReplaceText, { "Geography" }),
            AddTab = Table.AddColumn(ReplacedGeo2, "TabName", each name)
        in
            AddTab,

    // === STEP 4: Combine all tabs ===
    Combined = Table.Combine(List.Transform(TabNames, each GetSheet(_))),

    // === STEP 5: Filter out rows with zero or null Dollar Sales ===
    FilteredSales =
        Table.SelectRows(
            Combined,
            each [Dollar Sales] <> null and [Dollar Sales] <> 0
        ),

    // === STEP 5.1: Remove rows where BOTH Dollar Sales and Display Sales are null (true junk only) ===
    RemovedTrueNulls =
        Table.SelectRows(
            FilteredSales,
            each not (
                ((try [Dollar Sales] otherwise null) = null)
                    and ((try [Dollar Sales Display Only] otherwise null) = null)
            )
        ),

    // === STEP 6: Merge time info ===
    Merged = Table.NestedJoin(RemovedTrueNulls, { "TabName" }, IndexMap, { "TabName" }, "Index", JoinKind.LeftOuter),
    ExpandedIndex = Table.ExpandTableColumn(Merged, "Index", { "Time" }),

    // === STEP 7: Clean Geography ===
    Reordered =
        Table.ReorderColumns(ExpandedIndex, { "Time" } & List.RemoveItems(Table.ColumnNames(ExpandedIndex), { "Time" })),
    DuplicatedColumn = Table.DuplicateColumn(Reordered, "Geography", "Geography – Copy"),
    RenamedColumns2 = Table.RenameColumns(DuplicatedColumn, { { "Geography – Copy", "Clean Geography" } }),
    ReplacedValue =
        Table.ReplaceValue(RenamedColumns2, " DIV", "", Replacer.ReplaceText, { "Clean Geography" }),
    ReplacedValue1 =
        Table.ReplaceValue(ReplacedValue, "ADUSA ", "", Replacer.ReplaceText, { "Clean Geography" }),
    ReplacedValue2 =
        Table.ReplaceValue(ReplacedValue1, "-RMA - MASS", "", Replacer.ReplaceText, { "Clean Geography" }),
    ReplacedValue3 =
        Table.ReplaceValue(ReplacedValue2, "-RMA - WALMART", "", Replacer.ReplaceText, { "Clean Geography" }),

    // === ⚡ STEP 8: Buffer before grouping to speed up performance ===
    Buffered = Table.Buffer(ReplacedValue3),

    // === STEP 9: Group totals per Time + Clean Geography + Sub Category ===
    GroupedSubCat =
        Table.Group(
            Buffered,
            { "Time", "Clean Geography", "Sub Category" },
            {
                { "SubCatTotalSales", each List.Sum([Dollar Sales]), type number },
                { "SubCatTotalDisplaySales", each List.Sum([Dollar Sales Display Only]), type number }
            }
        ),

    // === STEP 9.1: Nullify zero SubCat totals (avoid divide-by-zero) ===
    FixedGroupedSubCat =
        Table.TransformColumns(
            GroupedSubCat,
            {
                { "SubCatTotalSales", each if _ = 0 then null else _, type nullable number },
                { "SubCatTotalDisplaySales", each if _ = 0 then null else _, type nullable number }
            }
        ),

    // === STEP 10: Merge SubCategory totals back ===
    MergedSubCat =
        Table.NestedJoin(
            Buffered,
            { "Time", "Clean Geography", "Sub Category" },
            FixedGroupedSubCat,
            { "Time", "Clean Geography", "Sub Category" },
            "SubCatTotals",
            JoinKind.LeftOuter
        ),
    ExpandedSubCat =
        Table.ExpandTableColumn(MergedSubCat, "SubCatTotals", { "SubCatTotalSales", "SubCatTotalDisplaySales" }),

    // === ⚡ STEP 11: Buffer again before second grouping ===
    Buffered2 = Table.Buffer(ExpandedSubCat),

    // === STEP 12: Group totals per Time + Clean Geography (Category level) ===
    GroupedCategory =
        Table.Group(
            Buffered2,
            { "Time", "Clean Geography" },
            {
                { "CatTotalSales", each List.Sum([Dollar Sales]), type number },
                { "CatTotalDisplaySales", each List.Sum([Dollar Sales Display Only]), type number }
            }
        ),

    // === STEP 12.1: Nullify zero Category totals ===
    FixedGroupedCategory =
        Table.TransformColumns(
            GroupedCategory,
            {
                { "CatTotalSales", each if _ = 0 then null else _, type nullable number },
                { "CatTotalDisplaySales", each if _ = 0 then null else _, type nullable number }
            }
        ),

    // === STEP 13: Merge Category totals back ===
    MergedCategory =
        Table.NestedJoin(
            Buffered2,
            { "Time", "Clean Geography" },
            FixedGroupedCategory,
            { "Time", "Clean Geography" },
            "CatTotals",
            JoinKind.LeftOuter
        ),
    ExpandedCategory =
        Table.ExpandTableColumn(MergedCategory, "CatTotals", { "CatTotalSales", "CatTotalDisplaySales" }),

    // === STEP 14: Add constant columns for SubCategory & Category Sales ===
    AddSubCatSales = Table.AddColumn(ExpandedCategory, "Sub Category Sales", each [SubCatTotalSales], type number),
    AddSubCatDisplaySales =
        Table.AddColumn(AddSubCatSales, "Sub Category Display Sales", each [SubCatTotalDisplaySales], type number),
    AddCatSales = Table.AddColumn(AddSubCatDisplaySales, "Category Sales", each [CatTotalSales], type number),
    AddCatDisplaySales =
        Table.AddColumn(AddCatSales, "Category Display Sales", each [CatTotalDisplaySales], type number),

    // === STEP 15: Remove helper columns ===
    RemovedHelpers =
        Table.RemoveColumns(
            AddCatDisplaySales,
            { "SubCatTotalSales", "SubCatTotalDisplaySales", "CatTotalSales", "CatTotalDisplaySales" }
        ),

    // === STEP 16: Replace nulls with 0 for additive numeric columns ===
    ReplacedNulls =
        Table.ReplaceValue(
            RemovedHelpers,
            null,
            0,
            Replacer.ReplaceValue,
            {
                "Dollar Sales",
                "Dollar Sales Display Only",
                "Dollar Share of SubCategory",
                "Dollar Share of SubCategory Display Only",
                "Dollar Share of Category",
                "Dollar Share of Category Display Only"
            }
        ),
    #"Replaced Value" = Table.ReplaceValue(ReplacedNulls," LOAF SLICE","",Replacer.ReplaceText,{"Product"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value"," 1 CTD","",Replacer.ReplaceText,{"Product"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1"," LOAF THIN SLICE"," THIN",Replacer.ReplaceText,{"Product"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2"," 1 CT","",Replacer.ReplaceText,{"Product"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value3", "Product", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Product.1", "Product.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Product.1", Int64.Type}, {"Product.2", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each true),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Product.2", "Product"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "1 Shr Point of Display $ Opp", each [Dollar Sales Display Only] * .01),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Product.1", type text}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Product.1", "UPC"}})
in
    #"Renamed Columns1"

Thanks