Issue with SUMMARIZE and ADDCOLUMNS

Hi JamesMcEwan ,

Thanks for some_bih's reply!
And JamesMcEwan , just as some_bih said, since you didn't provide any data model, I'm not sure if I understand your question correctly, nor if the DAX I provided is feasible. Could you please try this:

DEFINE
    -- User-selected film for comparison (will replace with selected value)
    VAR _myFilmInput = 130429

    -- Retrieve the maximum playweek ID for the selected film (playweekID = the # of days from a film has played)
    VAR _myMaxDayFilter = LOOKUPVALUE(Film[MaxPlayweekID], Film[rentrakReleaseID], _myFilmInput)

    -- Step 1: Summarize salesGross per film per playweek
    -- This aggregates total gross per release ID and playweek (25-week run) (25-Week and weeks over 25 are marked as 26)
    VAR _mytable =
        SUMMARIZE(
            FILTER(
                filmSales,
                RELATED(Film[FilminScope Global])  -- Ensures only films within scope are considered (this checks if the films start date is after the fact table start date)
                && filmSales[playweekID] <= _myMaxDayFilter  -- Filters to valid days matching only the days of the selected film
                && filmSales[playweekID] > 0  -- for this specific pattern this exlcludes previews (which are marked as 0 on the playweekID table)
            ),
            filmSales[rentrakReleaseID],  -- Groups by release ID
            Playweek[25 Week Run],  -- Groups by playweek (ensuring 25-week alignment)
            "salesGross", SUM(filmSales[salesGross])  -- Aggregates total sales for that film and week (aligned with the days of the selected film)
        )

    -- Step 2: Compute `filmGross` by retrieving the selected film's gross for each playweek
    -- This ensures we get the total gross of the chosen film for a matching playweek
    VAR _myWithFilmGross =
        ADDCOLUMNS(
            _mytable,
            "filmGross",
            VAR _Current25WeekRun = [25 Week Run]  -- Store the current playweek context
            RETURN
                CALCULATE(
                    SUM(filmSales[salesGross]),  -- Sum salesGross for the selected film
                    filmSales[rentrakReleaseID] = _myFilmInput,  -- Only for the chosen film
                    Playweek[25 Week Run] = _Current25WeekRun,  -- Match playweek within 25-week range
                    filmSales[playweekID] <= _myMaxDayFilter,  -- Ensure valid playweeks (may be redundant)
                    filmSales[playweekID] > 0  -- Ignores previews
                )
        )

    -- Step 3: Compute `deltaGross`, which is the absolute difference between salesGross and filmGross
    -- This measures how similar each film's weekly performance is to the selected film
    VAR _myCombined =
        ADDCOLUMNS(
            _myWithFilmGross,
            "deltaGross", ABS([salesGross] - [filmGross])  -- Compute absolute difference for each week
        )

    -- Step 4: Summarize `_myCombined` by `rentrakReleaseID` and calculate the sum of `deltaGross`
    VAR _mySummarized =
        SUMMARIZE(
            _myCombined,
            filmSales[rentrakReleaseID],
            "TotalDeltaGross", SUM([deltaGross])  -- Sum of deltaGross for each film
        )

    -- Step 5: Retrieve the top 5 films with the lowest total deltaGross
    VAR _myTop5Films =
        TOPN(
            5,
            _mySummarized,
            [TotalDeltaGross], ASC  -- Sort by TotalDeltaGross in ascending order
        )
EVALUATE
    _myTop5Films

If it is incorrect, please provide sample data for each table, the relationship between the tables, and your expected results. This will help us understand your problem. Thank you!

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.