DAX: min value from family

Question

I have a table in power pivot and I want to know the minimum value for each family (here I show 3, but I have more than 1000). For that, I want to add a new column (Min Family Value) in order to add more calculations then and so on.

Product ID | Family Product | Country | Value | Min Family Value (what I want)

 44856        Family1        UK     $10          $9.75     
 58977        Family2        USA    $11          $10.5
 77890        Family2        EU     $10.5        $10.5              
 44856        Family1        EU     $9.75        $9.75
 21213        Family3        AUS    $12          $11.5       
 16151        Family1        AUS    $10          $9.75           
 15963        Family3        EU     $11.5        $11.5           
 58977        Family2        UK     $10.5        $10.5

On the other hand, then I want to show all these values, calculations, in a pivot table and pivot charts. How can I do the DAX formula in order to have the minimum value when I apply filters in the pivot table (slicers, filters...)?

I did this formula but it doesn't works as I want, because it returns only the same value:

Min Family Value:= CALCULATE(
     MIN('TABLE'[VALUE]);ALLSELECTED(TABLE[Family Product])
)

Many thanks

RADO RADO · Accepted Answer · 2017-05-22T09:15:19

I assume you know the difference between a 'calculated column' and a 'measure'.

To make 'min family values' available for analysis in a pivot table, you need to create a measure:

Min_Family_Value:= MIN('MyTable'[Value])

If you want to add a calculated column to your table (so that you can use it as one of the filters):

Min_Value_By_Family:= 
   CALCULATE( MIN('MyTable'[Value]), 
              FILTER('MyTable', 
                     'MyTable'[Family Product] = EARLIER('MyTable'[Family Product]))
             )

DAX: min value from family

1 Answers