PowerPivot DAX-每组动态排名(每组最小值) [英] PowerPivot DAX - Dynamic Ranking Per Group (Min Per Group)
问题描述
我正在寻找一种在 Microsoft PowerPivot 2010 中使用的方法，该方法将允许我执行动态排名，该排名将根据所应用的过滤器和切片器值自动更新关联的排名值.
因此，我所看到的所有示例都利用Calculate()DAX函数，该函数通过All()函数覆盖PowerPivot表中的现有过滤器，这会导致忽略用户可以应用的预定义过滤器.
为说明我的要求，请参考以下示例:
(PowerPivot窗口中的源数据:)
-------------------------------------------------------------------------------------
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
------------------------------------------------------------------------------------------
现在，我希望能够仅基于第一个交易日期显示每个索赔编号和索赔人编号的美元金额.我本质上希望只显示与每个索赔号和索赔人编号组相关的第一笔交易相关的美元.
我的想法是为类似于[按-Row_Number()划分功能]函数的每个[索赔号+索赔人编号]组创建动态等级.这样，每个包含等级值1的[索赔号+索赔人编号]组将代表与此值相关的FIRST交易.
但是，如果用户对结果数据透视表中的数据应用筛选器，则需要更改此等级值.
下面可以根据上面定义的示例数据对此进行说明:
----------------------------------------------------------------------------------------------
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
------------------------------------------------------------------------------------------------
现在，如果用户通过切片器或通过数据透视表或PowerPivot表本身中的过滤器下拉列表应用了过滤器，则不包括评估日期< 2012年2月1日，排名值应自动更新并反映如下:
(已应用过滤器)
=============================================================================
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"相关的美元金额(基于任何用户应用的过滤器)每组我都会对您采用的任何方法敞开心ear.
尽管这是一个写得很好的问题，您显然已经花了很多时间在制定上，但您应该 SkyDrive 上发布我的作品.
Jacob
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.
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
这篇关于PowerPivot DAX-每组动态排名(每组最小值)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
