数据透视图累积(运行)不重复计数 [英] Pivot chart cumulative (running in) distinct count

问题描述

我需要创建一个数据透视表(为了非常方便的过滤和效率，它应该是数据透视表，但是可以做任何等效的事情)来显示累积的独立计数.例如，我有一个数据集:

I need to create pivot chart (it is supposed to be pivot for the sake of very convenient filtering and efficiency but anything equivalent could do) showing cumulative distinct count. For example I have a data set:

Month ¦ Drink brand ¦ Drink type
--------------------------------
1     ¦ Abc         ¦ Water
1     ¦ Def         ¦ Soft
1     ¦ Abc         ¦ Water
1     ¦ Ghi         ¦ Soft
1     ¦ Xyz         ¦ Water
2     ¦ Abc         ¦ Water
2     ¦ Abc         ¦ Water
2     ¦ Jkl         ¦ Soft
2     ¦ Opq         ¦ Soft
2     ¦ Abc         ¦ Water

然后我想得到一张图表:

And from this I would like to get a chart:

           ¦
Drink      ¦
type       ¦            S
cumulative ¦            []
unique     ¦ W  S    W  []
count      ¦ [] []   [] []
           ¦_[]_[]___[]_[]_
               1       2
                 Month

我尝试在值字段设置"中使用汇总值依据"->非重复计数"和将值显示为"->运行总计"，但是运行"选项似乎不了解非重复计数背后的原理并简单地添加每个月的非重复计数.

I have tried using "Summarize Values By" -> "Distinct Count" and "Show values as" -> "Running Total In" in Value Field Settings but the running in option seems to be ignorant of the philosophy behind distinct count and simply adds the distinct counts for each month.

推荐答案

我已经设法使用Power Pivot和DAX解决了这个特殊问题.

I have managed to solve this particular problem using power pivot and DAX.

以下: http://www.daxpatterns.com/cumulative-total/以及提供的累计总数示例

Following this: http://www.daxpatterns.com/cumulative-total/ and the supplied example of cumulative total

Cumulative Quantity :=
CALCULATE (
    SUM ( Transactions[Quantity] ),
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)

我创建了两个新的计算字段("POWERPIVOT"->计算字段")，"Water_cumulative_count":

I have created two new calculated fields ("POWERPIVOT" --> "Calculated Fields"), "Water_cumulative_count":

=CALCULATE (
    DISTINCTCOUNT( Range[Drink brand] ),
    FILTER (
        ALL ( Range[Month] ),
        Range[Month] <= MAX ( Range[Month] )
    ),
    FILTER (
        ALL ( Range[Drink type] ),
        Range[Drink type] = "Water"
    )
)

和类似的"Soft_cumulative_count".

and analogic "Soft_cumulative_count".

然后我只是将这些新字段添加到数据透视表中.

Then I simply added these new fields to the pivot.

虽然解决了这个特定问题，但我对这种解决方案的可调用性不满意.如果我有很多饮料类型"，那么创建那么多新的计算字段将是非常无效的.我想知道是否有更好的方法使用DAX来解决它.也许...但是我是这个主题的1天新手.

Whilst it solves this particular problem I am not happy with low scallability of this solution. If I had many "Drink types" it would be very ineffective to create as many new calculated fields. I wonder if there is a better way to solve it using DAX. Perhaps... But I am a 1-day novice in this topic.

这篇关于数据透视图累积(运行)不重复计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！