Dax Measure Help

glmoon5 · ‎10-27-2025

Hello,

Yesterday I created a post and it was marked as Spam for some reason? I notified the moderator but have not heard anything back. I have never had this issue so I don't know the process.

Can anyone help me?

https://community.fabric.microsoft.com/t5/Desktop/Dax-Measure-Help/m-p/4858751#M1442399

glmoon5 · ‎10-26-2025

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.

Time	Product	Geography	Custom Subcategory Name	Dollar Sales	Dollar Sales Display Only	Dollar Share of Category	Dollar Share of Category Display Only	Dollar Share of SubCategory	Dollar Share of SubCategory Display Only	Sub Category Sales	Sub Category Display Sales	1 Shr Point of Display $ Opp	Variance $ Shr Display to $ Shr	Display $ OPP
L52	DECILS FAOL TC 1 ZO 72 FAOL MUIMERP/YTLAICEPS SDS & SNARG ELOHW 12 DAERB RELLIK SEVAD 1507204673100	Retailer A	FAOL	$347,281	$23,655	1.05	0.69	3.15	4.85	$9,794,402	$455,231	$237	1.70	$0.00
L52	ECILS NIHT FAOL TC 1 ZO 5.02 FAOL MUIMERP/YTLAICEPS SDS & SNARG ELOHW 12 DAERB RELLIK SEVAD 1317204673100	Retailer A	FAOL	$298,511	$17,098	0.90	0.50	2.71	3.50	$9,794,402	$455,231	$171	0.79	$0.00
L4	DECILSNU REGRUBMAH TC 8 ZO 6.71 SNUB HCIWDNAS ETIHW DAERB RELLIK SEVAD 1428204673100	Retailer A	SLLOR/SNUB HCIWDNAS	$64,685	$1,792	0.20	0.05	0.61	0.12	$122,159	$3,431	$18	-0.48	$8.68
L4	ECILS NIHT FAOL TC 1 ZO 5.02 FAOL MUIMERP/YTLAICEPS DEESREWOP DAERB RELLIK SEVAD 1917204673100	Retailer A	FAOL	$61,409	$3,232	0.19	0.09	0.56	0.66	$106,453	$6,536	$32	0.11	$0.00
L4	DECILSNU REGRUBMAH TC 8 ZO 6.71 SNUB HCIWDNAS SDS & NIARG ELOHW 12 DAERB RELLIK SEVAD 1328204673100	Retailer A	SLLOR/SNUB HCIWDNAS	$57,474	$1,639	0.17	0.05	0.54	0.11	$122,159	$3,431	$16	-0.43	$7.00
L4	DECILS FAOL TC 1 ZO 52 FAOL MUIMERP/YTLAICEPS DEESREWOP DAERB RELLIK SEVAD 1827204673100	Retailer A	FAOL	$45,044	$3,303	0.14	0.10	0.41	0.68	$106,453	$6,536	$33	0.27	$0.00
L4	TC 5 ZO 57.61 SLEGAB YRREB NIMOOB DAERB RELLIK SEVAD 1018204673100	Retailer A	SMETI TSAFKAERB	$43,997	$956	0.13	0.03	1.05	0.44	$71,031	$987	$10	-0.61	$5.82
L13	ECILS NIHT FAOL TC 1 ZO 5.02 FAOL MUIMERP/YTLAICEPS TAEHW ELOHW %001 DAERB RELLIK SEVAD 1027204673100	Retailer A	FAOL	$30,234		0.09		0.27		$637,119	$2,966	$0	-0.27	$0.00

I have 3 custom columns:

Sub Category Sales
Sub Category Display Sales
1 Shr Point of Display $ Opp

Then my 4 Dax Measures

Dollar Share of Subcategory (m) =
Dollar Share of Subcategory Display Only (m) =
1 Shr Pt of Display $ Opp =
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"

Dollar Share of Subcategory (m) =

DIVIDE(

SUM(Display[Sub Category Sales]),

CALCULATE(

SUM(Display[Dollar Sales]),

ALL(Display)

)

) * 100

Dollar Share of Subcategory Display Only (m) =

DIVIDE(

SUM(Display[Sub Category Display Sales]),

SUM(Display[Dollar Sales Display Only])

) * 100

1 Shr Pt of Display $ Opp =

SUM('Display'[Dollar Sales Display Only]) * 0.01

Variance $ Shr Display to $ Shr =

([Dollar Share of Subcategory Display Only (m)] - [Dollar Share of Subcategory (m)])

Display $ OPP =

SUMX(

ADDCOLUMNS(

VALUES('Display'[Product]),

"@OnePt", CALCULATE(MAX('Display'[1 Shr Point of Display $ Opp])),

"@Variance", [Variance $ Shr Display to $ Shr] // measure directly

),

[@OnePt] * [@Variance]

)

Thanks

MFelix · ‎10-27-2025

Hi @glmoon5

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

KarinSzilagyi · ‎10-27-2025

Hi @glmoon5, I've also had the issue before that a response to a question was flagged as spam. It took a bit over a day to get my post un-flagged after I contacted the mods.

According to the response I received it was flagged by the automatic scan. I've learned since then that common triggers can be:

Copying and pasting content into the message field (I personally assume this only applies if it's the whole or most of a message, since I haven't encountered that issue when pasting code snippets so far)
Multiple edits (this was probably the reason why mine got flagged back then)
External links

I was told that there isn't really any way to know why your post specifically got detected as spam.

If you've already contacted the Moderators to get it un-flagged there isn't really much you can do in the meantime until you get a response. If this: https://community.fabric.microsoft.com/t5/Desktop/Dax-Measure-Help/m-p/4858751#M1442399 (= the post you linked to in your original message) was the post that was flagged, it already was unflagged, as I can see it too!

Did I answer your question? If so, please consider marking my response as the ‘Accepted Solution’ - it helps others with the same issue find the answer more easily!

glmoon5 · ‎10-27-2025

@KarinSzilagyi Thank you. They just unflagged it and yes many of the reasons you mentioned are why mine would have been flagged. I pasted print screens, code, excel sample data etc.....Is there no way to simply attached Excel file or the .pbix file directly?

KarinSzilagyi · ‎10-27-2025

Unfortunately, attaching files directly is only enabled for specific user roles. I'm not 100% sure whether that's just limited to MVPs and Community Support or Super Users too.

Did I answer your question? If so, please consider marking my response as the ‘Accepted Solution’ - it helps others with the same issue find the answer more easily!