DAX formula not producing the expected results

[hermanj]

I'm having trouble with a DAX formula that I use to create an index. This index is a cumulative product of a weighted variation for each period up to the current period.

The issue is that while the DAX formula works as expected at the total level, it doesn't allow me to slice the results by the 'Product' column. This is likely due to the use of FILTER and ALL, but I haven't found a way to maintain the expected results at the total level while also getting accurate results when sliced by 'Product'.

For the purpose of explaining my problem, I've simplified the case to a single table, so there are no table relationships to consider.

This is an extract of my data:

Product	Period	Variation	Weight
A	01/06/2023	1,5%	46,1%
B	01/06/2023	-0,6%	53,9%

Below is the DAX formula i wrote:

Date Index = 
CALCULATE (
    PRODUCTX (
        SUMMARIZECOLUMNS (
            'Table'[Period],
            "Index", 
                SUMX (
                    'Table', 
                    'Table'[Variation] * 'Table'[Weight]
                )
        ),
        [Index] + 1
    ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Period] <= MAX ( 'Table'[Period] )
    )
)

Thank you for your support.

[MFelix]

Try the following code:

Date Index V2 = 
var _periodCalc =     PRODUCTX (
        SUMMARIZECOLUMNS (
            'Table'[Period],
            "Index", 
                SUMX (
                    'Table', 
                    'Table'[Variation] * 'Table'[Weight]
                )
        ),
        [Index] 
    )

var _periodCalcTotal = CALCULATE (    PRODUCTX (
        SUMMARIZECOLUMNS (
            'Table'[Period],
            "Index", 
                SUMX (
                    'Table', 
                    'Table'[Variation] * 'Table'[Weight]
                )
        ),
        [Index] + 1
    ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Period] <= MAX ( 'Table'[Period] )
    )
)
Return

IF( ISFILTERED('Table'[Product]) || ISFILTERED('Table'[Period].[Date]) , _periodCalc, _periodCalcTotal)

[hermanj]

Thanks a lot @MFelix 

[MFelix]

Don't forget to accept the correct answer to help out others. 

[DataNinja777]

Hi @hermanj ,

The issue arises because the FILTER and ALL in your DAX formula remove the filter context from the Product column, which prevents the calculation from being specific to each product when slicing by it. To fix this while maintaining the correct results at both the total and product levels, you need to modify your formula to respect the Product filter context.

Here’s an updated version of your formula that addresses this:

Date Index = 
VAR CurrentProduct = SELECTEDVALUE('Table'[Product])
RETURN
CALCULATE (
    PRODUCTX (
        SUMMARIZECOLUMNS (
            'Table'[Period],
            'Table'[Product],
            "Index", 
                SUMX (
                    FILTER(
                        'Table',
                        'Table'[Product] = CurrentProduct
                    ),
                    'Table'[Variation] * 'Table'[Weight]
                )
        ),
        [Index] + 1
    ),
    FILTER (
        ALL ( 'Table'[Period] ),
        'Table'[Period] <= MAX ( 'Table'[Period] )
    )
)

• The CurrentProduct variable captures the selected product in the current filter context.
• The FILTER function inside the SUMX ensures the calculations are performed only for the current product in the context.
• This approach ensures that the Date Index updates correctly for each product while maintaining the total calculation logic.

Try this and let me know if the issue persists!

Best regards,

[hermanj]

Hi @DataNinja777! Thanks a lot for your answer. With your corrections, the formula works perfect at the products level, but it seems like at the total values per month i have no results:

Any ideas why this is?

[DataNinja777 · Accepted solution]

Hi @hermanj ,

You can adjust the formula to calculate totals by detecting when CurrentProduct is BLANK() (indicating the total context) and applying a slightly different logic for such cases.

Here’s the revised formula:

Date Index = 
VAR CurrentProduct = SELECTEDVALUE('Table'[Product])
RETURN
IF (
    ISBLANK(CurrentProduct),
    CALCULATE (
        PRODUCTX (
            ADDCOLUMNS (
                SUMMARIZE(
                    'Table',
                    'Table'[Period]
                ),
                "Index",
                SUMX (
                    ALL('Table'), -- Include all products for totals
                    'Table'[Variation] * 'Table'[Weight]
                )
            ),
            [Index] + 1
        ),
        REMOVEFILTERS('Table'[Period]),
        'Table'[Period] <= MAX('Table'[Period])
    ),
    -- Product-level logic
    CALCULATE (
        PRODUCTX (
            ADDCOLUMNS (
                SUMMARIZE(
                    'Table',
                    'Table'[Period]
                ),
                "Index",
                SUMX (
                    FILTER (
                        'Table',
                        'Table'[Product] = CurrentProduct
                    ),
                    'Table'[Variation] * 'Table'[Weight]
                )
            ),
            [Index] + 1
        ),
        REMOVEFILTERS('Table'[Period]),
        'Table'[Period] <= MAX('Table'[Period])
    )
)

How It Works

• At the Product Level:
• The formula uses the existing CurrentProduct logic to calculate the index accurately for each product.
• At the Total Level:
• The formula aggregates data across all products by bypassing the product-specific filter (ALL('Table')).

Best regards,

[hermanj]

Thanks a lot @DataNinja777