PowerPivot DAX: Identify Max & Min Value per Group

Question

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

Customer
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.

score 2 · Answer 1 · answered Oct 29 '15 at 14:17

2

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().

answered Oct 29 '15 at 14:17

greggyb

3,728
1
11
32

score 1 · Answer 2 · answered Aug 27 '13 at 21:28

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

aesthetic_a · Answer 3 · 2013-08-15T23:16:49.353

0

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?

edited Aug 15 '13 at 23:16

answered Aug 15 '13 at 07:18

aesthetic_a

11
2

1

I appreciate your quick reply. I am required however to have the **[Transaction Sequence No]** column **also** present within the PivotTable alongside the **[Customer]** and **[Store]** columns defined above. In addition to the columns I listed above in my example, I also have a measure named **[Total Cost]**. My end goal is to identify the min and max **[Transaction Sequence No]** value for each **group** and then display in a seperate calculated measure the **[Total Cost]** value associated with the particular **[Transaction Sequence No]** value in question. – user1159554 Aug 15 '13 at 08:53
I would need the **[Customer], [Store], and [Transaction Seq No]** columns present within the PivotTable side by side. The solution you kindly provided does not list the max and min **[Transaction Seq No]** value alongside the **[Customer]** and **[Store]** fields. Is there a way to place the min and max of the **[Transaction Seq No]** value alongside the actual **[Transaction Seq No]** field? Please reference the last table PivotTable example I listed above as that is how I would need the PivotTable to look like. I appreciate your help! – user1159554 Aug 15 '13 at 08:58

PowerPivot DAX: Identify Max & Min Value per Group

3 Answers3