CODEWITHSUNDEEP
excelpowerpivot
detrraxic
detrraxic

Reputation: 190

DAX formula to find MIN and % difference

I need help with using correct DAX formula to find the minimum value in column and then evaluating each row value based off of that minimum (how much higher is each number in %).

My table looks as follows:

https://1drv.ms/u/s!AlRG3cDrsAZTgeF4aurEanDadvVa3w?e=NKWlRQ

I am trying to obtain results in third and fourth columns that are based off of second column.

Upvotes: 0

Views: 258

Answers (1)

greggyb
greggyb

Reputation: 3798

DAX calculated columns:

Minimum = MIN ( 'Table'[Sedan] )

% Difference = ( 'Table'[Sedan] - 'Table'[Minimum] ) / 'Table'[Sedan]

Make sure to set the column data format to % for the second.

Aggregate functions are not affected by row context, so when we define 'Table'[Minimum] it's just grabbing the whole-table min. Then, we can just use the newly calculated column in a simple % difference with the existing 'Table'[Sedan]. In row context, column references are the value for the current row.

EDIT: Per comments, the goal is for this to interact with filters, so it cannot be in a calculated column.

Measures below:

Your min will depend on how you're building your visuals and reports, which you haven't shared with us. Two versions below:

//v1
Minimum = MIN ( 'Table'[Sedan] )

//v2
Minimum = CALCULATE ( MIN ( 'Table'[Sedan] ), ALLSELECTED ( 'Table' ) )

And the % difference. Again this will depend on how you're building reports, which you haven't shared. The first version works with the first version above, assuming you're looking at many rows at a time and you want the average % difference:

//v1
% Difference =
VAR MinValue = [Minimum]
RETURN
  AVERAGEX (
    'Table',
    ( 'Table'[Sedan] - MinValue ) / MinValue
  )

With the second version, I assume you're putting 'Table'[Sedan] into a visual so you're seeing distinct values:

//v2
% Difference =
VAR CurrentSedan = SELECTEDVALUE ( 'Table'[Sedan] )
VAR MinValue = [Minimum]
RETURN
  (CurrentSedan - MinValue) / MinValue

Upvotes: 2

Related Questions