Calculate the average of the median %-change per product using only a measure in DAX

Question

I need to perform a simple calculation in DAX that consists of 2 steps.

Step 1: Calculate the median price change (%) per product.

Step 2: Calculate the average of all the medians calculated in Step 1 while counting each product (calculated median) only once.

Using a calculated column for Step 1 doesnt seem to work out for me because the medians from Step 1 are subject to change as soon as the user filters the table (i.e. using a date slicer) in the report.

Therefore, I will need to get both Steps performed dynamically in a measure.

I'm pretty sure the answer to: "Is there any way to achieve this?" will be "Yes". I just lack the know-how at this point and can't seem to find the answer.

Agustin Palacios Agustin Palacios · Accepted Answer · 2020-07-15T21:26:55

I would recommend to create 2 differents measures to solve this problem. But since you need to create only 1 measure you can use the following DAX function:

Median_per_product = 

VAR __step1 = CALCULATE( MEDIAN('Table'[Price_Change] ), ALLEXCEPT('Table', 'Table'[Product] ))
VAR __step2 = AVERAGEX( VALUES( 'Table'[Product] ), CALCULATE( MEDIAN( 'Table'[Price_Change] ) ) )

RETURN 
IF( 
    HASONEVALUE('Table'[Product]) && 
    NOT(ISBLANK( __step1 )), 
    __step1,
    __step2 
)

This is the expected result:

Calculate the average of the median %-change per product using only a measure in DAX

1 Answers