Distinct cumulative count - PowerPivot, calculated fields and DAX

Question

I have struggled with this problem: Pivot chart cumulative (running in) distinct count

I am copying it here for convenience...

The idea was to create a cumulative distinct count using this 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 the required chart:

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

I have managed to solve the problem by using PowerPivot and DAX (Data Analysis Expressions) by creating two calculated fields. For "Water" it has been:

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

The problem I have with this solution is its low scallability (the need to create as many new calculated fields as there are "Drink types"). Is there a way to create the same result using DAX with just one calculated field or similar freature? Is is possible to group the result by "Drink type" in just one expression?

greggyb greggyb · Accepted Answer · 2016-01-19T14:03:43

No need to create a filter in the measure for [Drink Type]. Just make your chart a clustered column / bar, and use both [Month] and [Drink Type] as axis labels. Pivot charts can easily handle multiple labels that will nest appropriately.

Edit

Here's the pivot for a stacked column:

Distinct cumulative count - PowerPivot, calculated fields and DAX

1 Answers