0

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?

Community
  • 1
  • 1
zaptask
  • 687
  • 1
  • 8
  • 18

1 Answers1

1

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:

enter image description here

greggyb
  • 3,728
  • 1
  • 11
  • 32
  • Thank you. In my real world problem I am using stacked column chart. Is there a way to do it for this particular chart type? Also can I apply custom ordering of [Drink types] (in clustered or stacked) if I use this solution? – zaptask Jan 19 '16 at 14:12
  • Sure, you can do the same with stacked columns / bars. I just made that specific suggestion based on your mock-up. You can sort by another column in the Power Pivot model: http://blogs.technet.com/b/jessmeats/archive/2012/06/12/powerpivot-sort-by-column.aspx – greggyb Jan 19 '16 at 14:39
  • When I am just changing the chart type to "stacked" I still have two separate columns in each month. Isn't it so that the stacking happens within the groups defined by AXIS fields and since [Drink types] and [Month] are defining these groups they cannot be stacked? – zaptask Jan 19 '16 at 15:12
  • 1
    See edit for an image of a stacked column pivot chart. – greggyb Jan 19 '16 at 16:00
  • Ok, I've got it. In my case I used distinct cumulative count not total cumulative count but it's all good. Thank you very much for your help. – zaptask Jan 19 '16 at 16:19