Total row calculates in filter context that is wrong

[jja]

Hi

I have followinf formula:
Profit =
VAR AdjustedRows =
FILTER(
Statistics,
Statistics[Date] >= DATE(2024, 11, 1)
&& RELATED(Stockcodes[Filter ProdCat]) IN {"1.1 No Sales", "2.1 Too much"}
)
VAR AdjustedProfit =
SUMX(
AdjustedRows,
((Statistics[ST03020] * Statistics[ST03021]) * (1 - Statistics[ST03022] / 100))
- (Statistics[ST03020] * Statistics[ST03023])
)
VAR RegularProfit =
SUMX(
Statistics,
((Statistics[ST03020] * Statistics[ST03021]) * (1 - Statistics[ST03022] / 100))
- (Statistics[ST03020] * Statistics[ST03023])
)
RETURN
IF(
NOT ISEMPTY(AdjustedRows),
IF(AdjustedProfit < 0, 0, AdjustedProfit),
RegularProfit
)

Formula works fine for rows but for total it returns not the correct value. Seems it keeps Statistics[Date] >= DATE(2024, 11, 1) and Stockcodes[Filter ProdCat]) IN {"1.1 No Sales", "2.1 Too much"}.

I tried removing filters from Statistics[Date] >= DATE(2024, 11, 1) and Stockcodes[Filter ProdCat]) IN {"1.1 No Sales", "2.1 Too much"} but probably also issue here that in filter pane there is this filter Filter ProdCat that user will select and ofcourse same date filter that user will select.
So now the question is how to correctly calculate total row and keep this total to work in user filter context?

[Anonymous · Accepted solution]

Hi @jja , hello Bibiano_Geraldo, thank you for your prompt reply!

Please check the following measure:

Profit = 
VAR AdjustedRows =
FILTER(
'Table',
'Table'[Date] >= DATE(2024, 11, 1)
&& RELATED('Table2'[Filter ProdCat]) IN {"1.1 No Sales", "2.1 Too much"}
)
VAR AdjustedProfit =
SUMX(
AdjustedRows,
(('Table'[ST03020] * 'Table'[ST03021]) * (1 - 'Table'[ST03022] / 100))
- ('Table'[ST03020] * 'Table'[ST03023])
)
VAR RegularProfit =
SUMX(
'Table',
(('Table'[ST03020] * 'Table'[ST03021]) * (1 - 'Table'[ST03022] / 100))
- ('Table'[ST03020] * 'Table'[ST03023])
)
RETURN

IF(
    ISINSCOPE('Table'[ST03020]),
    IF(
        NOT ISEMPTY(AdjustedRows),
        IF(AdjustedProfit < 0, 0, AdjustedProfit),
        RegularProfit
    ),
    SUMX(
        ALL('Table'),
        (('Table'[ST03020] * 'Table'[ST03021]) * (1 - 'Table'[ST03022] / 100))
        - ('Table'[ST03020] * 'Table'[ST03023])
    )
)

Before:

After:

Best regards,

Joyce

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

[Bibiano_Geraldo]

Hi @jja ,

The issue you’re encountering with the total row in your formula is because the SUMX functions in your DAX formula are calculated row by row, but when you aggregate over the entire table (for the total), the filters you set within the FILTER expression (like Statistics[Date] >= DATE(2024, 11, 1) and Stockcodes[Filter ProdCat] IN {"1.1 No Sales", "2.1 Too much"}) are being applied globally, which isn’t what you want for the total.

To resolve this issue and calculate the correct total while respecting the user’s filters, you should handle the aggregation differently for the total row. Specifically, for the total row, you should remove the context filters (like Date and Filter ProdCat) in the calculation, so it uses the full table instead

Updated Formula:

Profit =
VAR AdjustedRows =
    FILTER(
        Statistics,
        Statistics[Date] >= DATE(2024, 11, 1)
            && RELATED(Stockcodes[Filter ProdCat]) IN {"1.1 No Sales", "2.1 Too much"}
    )
VAR AdjustedProfit =
    SUMX(
        AdjustedRows,
        (Statistics[ST03020] * Statistics[ST03021]) * (1 - Statistics[ST03022] / 100)
            - (Statistics[ST03020] * Statistics[ST03023])
    )
VAR RegularProfit =
    SUMX(
        Statistics,
        (Statistics[ST03020] * Statistics[ST03021]) * (1 - Statistics[ST03022] / 100)
            - (Statistics[ST03020] * Statistics[ST03023])
    )
VAR TotalAdjustedProfit =
    IF(
        ISFILTERED(Statistics[Date]) || ISFILTERED(Stockcodes[Filter ProdCat]),
        BLANK(),
        SUMX(
            FILTER(
                ALL(Statistics),  -- Removes all filters from Statistics table
                Statistics[Date] >= DATE(2024, 11, 1)
                    && RELATED(Stockcodes[Filter ProdCat]) IN {"1.1 No Sales", "2.1 Too much"}
            ),
            (Statistics[ST03020] * Statistics[ST03021]) * (1 - Statistics[ST03022] / 100)
                - (Statistics[ST03020] * Statistics[ST03023])
        )
    )
RETURN
    IF(
        NOT ISEMPTY(AdjustedRows),
        IF(AdjustedProfit < 0, 0, AdjustedProfit),
        IF(ISBLANK(TotalAdjustedProfit), RegularProfit, TotalAdjustedProfit)
    )

[jja]

Hi

I have tried similar approach and in this case total row will still be in filter context and will calculate only 

in this filter context.

Statistics[Date] >= DATE(2024, 11, 1)
                    && RELATED(Stockcodes[Filter ProdCat]) IN {"1.1 No Sales", "2.1 Too much"}

I am finishing "cracking my head" and probably will switch to either calculating Profit in Powerquery or i will create a calculated column. I am thinking now that this is not possible apply filter, then remove it for Tototal rows but again user should be able to apply this filter manualy

[Bibiano_Geraldo]

Hi @jja ,
You can modify the formula to explicitly handle the total row by checking if the current filter context is collapsed (like in totals). For this, use the HASONEVALUE function to differentiate between row-level and total contexts.

Here’s the updated DAX measure:

Profit =
VAR AdjustedRows =
    FILTER(
        Statistics,
        Statistics[Date] >= DATE(2024, 11, 1)
            && RELATED(Stockcodes[Filter ProdCat]) IN {"1.1 No Sales", "2.1 Too much"}
    )
VAR AdjustedProfit =
    SUMX(
        AdjustedRows,
        (Statistics[ST03020] * Statistics[ST03021]) * (1 - Statistics[ST03022] / 100)
            - (Statistics[ST03020] * Statistics[ST03023])
    )
VAR RegularProfit =
    SUMX(
        Statistics,
        (Statistics[ST03020] * Statistics[ST03021]) * (1 - Statistics[ST03022] / 100)
            - (Statistics[ST03020] * Statistics[ST03023])
    )
VAR TotalAdjustedProfit =
    SUMX(
        FILTER(
            ALL(Statistics),
            Statistics[Date] >= DATE(2024, 11, 1)
                && RELATED(Stockcodes[Filter ProdCat]) IN {"1.1 No Sales", "2.1 Too much"}
        ),
        (Statistics[ST03020] * Statistics[ST03021]) * (1 - Statistics[ST03022] / 100)
            - (Statistics[ST03020] * Statistics[ST03023])
    )
RETURN
    IF(
        HASONEVALUE(Statistics[Date]) && HASONEVALUE(Stockcodes[Filter ProdCat]),
        -- Row-level context
        IF(NOT ISEMPTY(AdjustedRows), IF(AdjustedProfit < 0, 0, AdjustedProfit), RegularProfit),
        -- Total row context
        TotalAdjustedProfit
    )