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