Pivot chart cumulative (running in) distinct count

Question

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.

score 1 · Answer 1 · answered Jan 19 '16 at 13:18

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

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

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

and analogic "Soft_cumulative_count".

Then I simply added these new fields to the pivot.

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.

I used your example in a "Measure", as opposed to a "Calculated Field", and it worked. The term "Calculated Field" might have been replaced with "Measure" for Power Pivot tables specifically. — Ken Love, May 05 '21 at 21:37

Pivot chart cumulative (running in) distinct count

1 Answers1

Linked