Geek asks and answers

PowerPivot DAX - Dynamic ranking by group (min. Per group)

I am looking for a method to use in Microsoft PowerPivot 2010 that will allow me to perform a dynamic ranking that will automatically update the associated rank value based on the filters and slicer values ​​applied.

Thus, all the examples I've seen use the DAX Calculate () function, which overrides existing filters in the PowerPivot table through the All () function, which calls predefined filters that users can apply to ignore.

To illustrate my requirements, refer to the example below:

(Source data in the 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
------------------------------------------------------------------------------------------

. , , № .

, [ + ], "partition by - Row_Number()". , [ + ], 1, , .

, , .

, :

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

, slicer PowerPivot, < 2/1/2012, , :

( )

=============================================================================
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
===========================================================================

, , ABCD123456, .

PowerPivot, , , 1, :

( , ​​ )

===============================================================================================
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

=============================================== ================================================

, , " " , , .

, - , .

, , - /, , , "VISIBLE" ( ) PER GROUP , .

+5
1

, , , , . , MSDN . .

-, [], . RANKX(), :

[Rank] = RANKX(
         FILTER(
         ALLSELECTED(Table1),Table1[Claimant Number]=max(Table1[Claimant Number])
                   ),
         [Amount],
            ,1)

- , [Amount] - ALLSELECTED() , FILTER() .

[Amount], , [Rank] 1:

[Opening Balance] = if([Rank]=1,[Amount],BLANK())

, , SkyDrive, .

Jacob

+3

More articles:


All Articles