Calculate DAX for a Context

[spartanboy]

Hi,

Below is a table where PERIOD_1 & PERIO_2 are selected by the user, hence these are from the original table columns, the section "CHANGE" is dynamic calculation performed per user selection on P1 & P2. I am able to calculate the COST_DELTA, but need help in creating a DAX measure for MIN_COST_DELTA for each SKU, and this value should remain Static irrespective to users selection of LOCATION using filters.

		PERIOD 1	PERIOD 2	CHANGE
SKU	LOCATION	COST	COST	COST_DELTA	MIN_DELTA_COST
ABC_123	A1	$    1.100	$    1.500	$           0.400	$                    (0.150)
ABC_123	A2	$    1.150	$    1.200	$           0.050	$                    (0.150)
ABC_123	A3	$    1.450	$    1.600	$           0.150	$                    (0.150)
ABC_123	A4	$    1.200	$    1.050	$         (0.150)	$                    (0.150)
ABC_123	A5	$    1.000	$    1.200	$           0.200	$                    (0.150)
DEF_234	B1	$    2.400	$    2.200	$         (0.200)	$                    (0.400)
DEF_234	B3	$    3.000	$    2.600	$         (0.400)	$                    (0.400)
DEF_234	B6	$    2.800	$    2.400	$         (0.400)	$                    (0.400)
DEF_234	B8	$    1.900	$    2.700	$           0.800	$                    (0.400)

[sevenhills · Accepted solution]

You can try this also:

To calculate difference in cost (measure)

Diff Cost = sum('Table'[Period 2 COST]) - Sum('Table'[Period 1 COST])

To calculate minimum of difference in cost in each SKU (measure):

Min Diff Cost by SKU = 
Minx ( FILTER( all ('Table'), 'Table'[SKU] = SELECTEDVALUE('Table'[SKU]))
       , [Diff Cost])

 Output:

[DataNinja777]

Hi @spartanboy ,

By using minx, the following measure calculates the minimum cost difference for each SKU across all locations, ignoring any LOCATION filter set by the user.

MIN_COST_DELTA =
CALCULATE (
    MINX (
        ALL ( 'YourTableName'[LOCATION] ),
        -- This ignores any filters on the LOCATION column
        'YourTableName'[COST_DELTA]
    ),
    ALLEXCEPT (
        'YourTableName',
        'YourTableName'[SKU]
    ) -- This keeps filters only on SKU
)

Best regards,