CODEWITHSUNDEEP
maxminpowerpivotdax
user1159554
user1159554

Reputation: 151

PowerPivot DAX: Identify Max & Min Value per Group

I am in need of identifying the maximum and minimum value per a defined group within a PowerPivot connected PivotTable.

Please reference the following base Excel table (posted via ASCII) that is linked to a corresponding PowerPivot table: 

-------------------------------------------------
Customer   |  Store  |  Transaction Sequence No | 
-------------------------------------------------       
     A           1                  1
     A           1                  2
     A           1                  3
     A           2                  1
     A           2                  2
     B           1                  1
     B           1                  2
     B           2                  1

As one can see, there are 2 columns above that uniquely define a group

  1. Customer
  2. Store

For each group, there can be one or more transactions, each with an associated [Transaction Sequence No] value as also outlined above.

Within the resulting PivotTable via 2 DAX Measures, I would like to list the minimum and maximum [Transaction Sequence No] value tied to each unique group.

This would result in the following PivotTable:

----------------------------------------------------------------------------
Customer   |  Store  |  Transaction Sequence No |   Max eq No | Min Seq No |
---------------------------------------------------------------------------- 
     A           1                  1                  3           1
     A           1                  2                  3           1
     A           1                  3                  3           1
     A           2                  1                  2           1
     A           2                  2                  2           1
     B           1                  1                  2           1
     B           1                  2                  2           1
     B           2                  1                  1           1

Now, it would be important for both of these new DAX measures to be dynamically updated if a user via a slicer or filter decides to apply a filter against the [Transaction Sequence No] column.

In this example, if the user opts to exclude a [Transaction Sequence No] value of 3 via an applied filter, the PivotTable should have its data updated to reflect the new max and min values:

----------------------------------------------------------------------------
Customer   |  Store  |  Transaction Sequence No |   Max eq No | Min Seq No |
---------------------------------------------------------------------------- 
     A           1                  1                  2           1
     A           1                  2                  2           1
     A           2                  1                  2           1
     A           2                  2                  2           1
     B           1                  1                  2           1
     B           1                  2                  2           1
     B           2                  1                  1           1

I am having trouble formulating the proper DAX syntax to accomplish this.

Upvotes: 4

Views: 42542

Answers (3)

greggyb
greggyb

Reputation: 3798

MinTSeq:=
CALCULATE(
    MIN('table'[Transaction Seq No])
    ALLSELECTED('table'[Transaction Seq No])
)

Should get you there. You can replace min with MAX().

More reading on ALLSELECTED().

Upvotes: 2

user1159554
user1159554

Reputation: 151

Thank you aesthetic_a for your kind response.

Unfortunately, your DAX statement does not show the min() value for each group, it is showing the min() value for each [Transaction Sequence No] value which is not would I was after.

As outlined within my original post (3rd table), I was in need of repeating the min() value tied to each group.

Your solution however pointed me in the right direction and I was able to accomplish what I needed.

FYI, my whole intention was to identify which [Transaction Sequence No] value contained the lowest value, and then sum ONLY the [Total Cost] measure tied to the lowest [Transaction Sequence No] value per group.

You pointed me in the right direction, and I used the following DAX statement to fulfill this requirement:

=countrows(filter(allselected(Table1[Transaction Sequence No]),[Sum of Transaction Sequence No](values(Table1[Transaction Sequence No])) >[Transaction Sequence No])) + 1

This DAX statement dynamically ranks each [Transaction Sequence No] value per group even if a filter is applied via a slicer against this dimensional attribute.

I then create another DAX measure that would ONLY return the [Total Cost] measure value if the associated Rank() value is equal to 1.

Finally, I then SUM this newly created measure above which would only return the [Total Cost] value tied to the first [Transaction Sequence No] value based on any user defined filters (via slicers, or Pivot Table filters) currently applied within the workbook.

Thank you, you pointed me in the right direction and I am grateful!

Take care

Upvotes: 1

aesthetic_a
aesthetic_a

Reputation: 11

Simply, two measures will provide the correct answer:

Measure for Minimum Sequence number: =CALCULATE(MIN('yourTable'[TransSeqNo]),VALUES('yourTable'[customer]))

Measure for Maximum Sequence number: =CALCULATE(MAX('yourTable'[TransSeqNo]),VALUES('yourTable'[customer]))

When you place these measures in the values field of a pivot table, with Customers and Store in the rows field, you can filter and get the correct result as mentioned.

EDIT

Unless I misunderstand what you are trying to do, the above measures will still achieve the last table in your question.

http://i43.tinypic.com/2j4eyaw.png

and

http://i44.tinypic.com/34fz5go.png

Is this not what you had in mind?

Upvotes: 0

Related Questions