DAX: calculate measure "without" current row

Question

I want to calculate a DAX measure "without" the current row. Here is an example based on AdventureWorksDW2014.

evaluate
    summarize(
        'FactInternetSales'

        , 'DimProduct'[ProductSubcategoryName]

Cell-level measures, these are defined as in https://docs.microsoft.com/en-us/sql/analysis-services/lesson-5-create-calculated-columns

        , "Margin", [InternetTotalMargin]       -- InternetTotalMargin := SUM([Margin])
        , "Cost", [InternetTotalProductCost]    -- InternetTotalProductCost := SUM([TotalProductCost])

I defined a new measure which is a composite (ratio) of existing measures:

        , "Gross Margin", [GrossMargin]         -- GrossMargin := DIVIDE([InternetTotalMargin], [InternetTotalProductCost])

Column totals:

        , "Total Margin", calculate([InternetTotalMargin], allselected('DimProduct'))
        , "Total Cost", calculate([InternetTotalProductCost], allselected('DimProduct'))

Column totals without current row, obtained by subtracting the cell value expression from the column total value expression:

        , "Total Margin w/o Subcat", calculate([InternetTotalMargin], allselected('DimProduct')) - [InternetTotalMargin]
        , "Total Cost w/o Subcat", calculate([InternetTotalProductCost], allselected('DimProduct')) - [InternetTotalProductCost]

This does all the aggregations before applying the formula:

        , "Total Gross Margin", calculate([GrossMargin], allselected('DimProduct'))

I want to calculate "Total Gross Margin" without the "current row". My first attempt yields the desired result but depends on explicit knowledge of the formula behind GrossMargin. This is "explicit" because we essentially re-implement the calculation after subtracting the "current row" out of the numerator and denominator. I want to be able to do this with measures where I don't know the formula, or the formula is potentially more complex than simple division.

        , "Total Gross Margin w/o Subcat (Explicit)",

            -- explicitly repeat the division formula
            divide(

                -- explicitly recompute the numerator
                calculate([InternetTotalMargin], allselected('DimProduct')) - [InternetTotalMargin]

                -- explicitly recompute the denominator
                , calculate([InternetTotalProductCost], allselected('DimProduct')) - [InternetTotalProductCost]
            )

        -- next step/where this is meant to be used:
        , "Gross Margin Impact (Explicit)", calculate([GrossMargin], allselected('DimProduct')) - divide(calculate([InternetTotalMargin], allselected('DimProduct')) - [InternetTotalMargin], calculate([InternetTotalProductCost], allselected('DimProduct')) - [InternetTotalProductCost])

I want a more general solution that does not require knowledge of the calculation, something like this:

        /*
        , "Gross Margin w/o Subcat", calculate([GrossMargin], allselected('DimProduct' Except Current Row)
        , "Gross Margin Impact, calculate([GrossMargin], allselected('DimProduct')) - calculate([GrossMargin], allselected('DimProduct' Except Current Row)
        */

Different failed attempts (using Margin rather than GrossMargin since it is easier to check):

        , "Trial 1", calculate([InternetTotalMargin], except(all('DimProduct'), 'DimProduct')) -- incorrect value
        , "Trial 2", calculate([InternetTotalMargin], allexcept('DimProduct', 'DimProduct'[ProductSubcategoryName])) -- incorrect value
        , "Trial 3", calculate([InternetTotalMargin], allexcept('DimProduct', 'DimProduct'[ProductCategoryName])) -- incorrect value
        , "Trial 4", calculate([InternetTotalMargin], allexcept('DimProduct', 'DimProduct'[ProductKey])) -- incorrect value
        , "Trial 5", calculate([InternetTotalMargin], 'DimProduct') -- incorrect
        , "Trial 6", calculate([InternetTotalMargin], 'DimProduct'[ProductSubcategoryName] <> 'DimProduct'[ProductSubcategoryName]) -- illogical, and blank result
        -- , "Trial 7", calculate([InternetTotalMargin], 'DimProduct'[ProductSubcategoryName] <> [ProductSubcategoryName]) -- invalid syntax
        -- running out of ideas here
    )
;

Is this even possible in DAX?

Hila DG Hila DG · Accepted Answer · 2018-05-07T01:10:37

No, this is not possible in DAX. If you want to calculate a measure you need to define it first. You can't create a place holder/insert formula here/ and hope for the best. What you can do is use the keyword VAR to define your measure, calculate different measures at the time of calculation and pick the one that suits best. Alternatively and if possible, use the calling function to define the measure by a "replace" method.

DAX: calculate measure "without" current row

1 Answers