PowerPivot DAX - Dynamic Ranking Per Group (Min Per Group)
I am searching for a method to utilize within Microsoft PowerPivot 2010 that will allow me to perform dynamic ranking that will automatically update the associated rank value based on filters and slicer values that are applied.
Thusfar, all examples I have seen utilize the Calculate() DAX function that overrides existing filters within the PowerPivot table via the All() function which causes predefined filters that users may apply to be disregarded.
To illustrate my requirements, please reference the example below:
(Source Data within PowerPivot Window:)
-------------------------------------------------------------------------------------
Claim Number | Claimant Number | Transaction Date | Dollar Amount
------------------------------------------------------------------------------------
ABCD123456 4 1/1/2012 $145.23
ABCD123456 4 8/1/2012 $205.12
ABCD123456 4 9/1/2012 $390.74
VDSF123455 2 3/5/2012 $10.12
VDSF123455 2 9/5/2012 $50.12
VDSF123455 2 12/9/2012 $210.45
------------------------------------------------------------------------------------------
Now, I would like to have the capability of ONLY displaying the dollar amount for each claim number and claimant number based on the FIRST transaction date. I would essentially desire to only show dollars tied to the first transaction tied to each claim# and claimant number group.
My thought process was to create a dynamic rank for each [claim number + claimant number] group similiar to the "partition by - Row_Number()" function. In this way, each [claim number + claimant number] group containing a rank value of 1 would represent the FIRST transaction tied to this value.
However, I would need this rank value to change if a user applies a filter against the data within the resulting PivotTable.
This can be illustrated below based on the sample data defined above:
----------------------------------------------------------------------------------------------
Claim Number | Claimant Number | Transaction Date | Dollar Amount | Rank |
---------------------------------------------------------------------------------------------
ABCD123456 4 1/1/2012 $145.23 1
ABCD123456 4 8/1/2012 $205.12 2
ABCD123456 4 9/1/2012 $390.74 3
VDSF123455 2 3/5/2012 $10.12 1
VDSF123455 2 9/5/2012 $50.12 2
VDSF123455 2 12/9/2012 $210.45 3
------------------------------------------------------------------------------------------------
Now, if a user applies a filter via a slicer or via the filter dropdown within a PivotTable or within the PowerPivot table itself excluding valuation dates < 2/1/2012, the rank value should automatically update itself and be reflected as shown below:
(Filters applied)
=============================================================================
Claim Number | Claimant Number | Transaction Date | Dollar Amount | Rank |
============================================================================
ABCD123456 4 8/1/2012 $205.12 1
ABCD123456 4 9/1/2012 $390.74 2
VDSF123455 2 3/5/2012 $10.12 1
VDSF123455 2 9/5/2012 $50.12 2
VDSF123455 2 12/9/2012 $210.45 3
===========================================================================
As you can see, the rank value was automatically updated for the first claim group based on one of the rows tied to claim number ABCD123456 being hidden, based on the user applied filter.
I would then like to create an additional measure within the PowerPivot table that would display the dollar amount ONLY tied to the transaction containing a rank value of 1 as shown below:
(Filters applied, New Measure Added)
===============================================================================================
Claim Number | Claimant Number | Transaction Date | Dollar Amount | Rank | Opening Dollar Amt|
================================================================================================
ABCD123456 4 8/1/2012 $205.12 1 $205.12
ABCD123456 4 9/1/2012 $390.74 2
VDSF123455 2 3/5/2012 $10.12 1 $50.12
VDSF123455 2 9/5/2012 $50.12 2
VDSF123455 2 12/9/2012 $210.45 3
===============================================================================================
A corresponding Pivot Table could then be created referencing this data so that the "Opening Dollar Amt" measure would only represent dollars tied to the FIRST transaction per group as outlined above.
I would greatly appreciate if someone can outline how this could be accomplished.
I thought the dynamic ranking approach might be a good idea for this, but if anyone has a better idea to achieve my end result/goal which is to simply obtain the dollar amount tied to the first transaction "VISIBLE" (based on any user applied filters) PER GROUP I would be open ears to whatever approach you may have.
Answer
Although this is a well written question that you've obviously put time into formulating, you should read this about cross posting in forums. This is a clear duplication of something you've posted on MSDN at exactly the same time. I've answered in both seeing as its a decent question.
Firstly I created a basic measure [Amount] to sum the dollar amount column. I then used that within RANKX() to create the following:
[Rank] = RANKX(
FILTER(
ALLSELECTED(Table1),Table1[Claimant Number]=max(Table1[Claimant Number])
),
[Amount],
,1)
The key is the table that the [Amount] measure is iterated over - ALLSELECTED() just brings the stuff in the current filter context into play and the expression within the FILTER() restricts the table to the current claim number.
After that it was a simple task to return the [Amount] based on whether or not the [Rank] was 1:
[Opening Balance] = if([Rank]=1,[Amount],BLANK())
Hope this makes sense, I posted my workings on SkyDrive if they help.
Jacob