none
Trying to get Running Distinct count of records based on a particular field by Month

    Question

  • I have a list of accounts which have bought a particular product by month.  I have approximately 4,200 rows in my PowerPivot table.  Columns contain how much volume they purchased, the acct #, and the month they purchased it.  Month is a numerical value from 1-12 (signaling 1 = Jan, 2 = Feb, 3 = Mar, and so on...) and is not in any type of date format.  Example of data below:

    Volume |    Acct #  | Month

    1          |     123     |    1 

    1          |     123     |    2

    2          |     123     |    3

    3          |     122     |    1

    1          |     122     |    2

    3          |     121     |    1

    I'm trying to show a distinct count of acct #'s by month, but I want the total to be a running total.  I created a measure called MM 2013: =countrows(DISTINCT('MM 2013'[Acct #])) that gives me distinct count of acct #'s in each particular month.  The formula returns me the below results.  I need to write a formula that gives running distinct counts, so would return 1442 for Month 1, 1864 for Month 2, then 2041 for Month 3.  Any help appreciated!

    Row Labels  |  MM 2013

    1                  |    1442

    2                  |    1479

    3                  |    1279

    Grand Total  |    2041

    Thursday, March 14, 2013 10:34 PM

Answers

  • Tanrou, without more data its a little difficult to test but your formula should look something like this:

    =CALCULATE([MM2013],
        ALL('MM2013'[Month]),
            FILTER(ALL('MM2013'[Month]),'MM2013'[Month]<=max('MM2013'[Month]))
               )
       

    This is based on Rob Collie's 'Greatest Formula in the World' (google it, I can't add links) and basically uses the FILTER() to create a context where the original month context is opened out using ALL() and it then just queries rows from the table where the month is less than or equal to the context month in your Pivot. The explanation of this technique in Rob's book is worth the price alone!!

    A couple of caveats:

     - My assumption is that you aren't using a separate date table (although you should be).

     - if your data has mulitple years that will be problematic, suggest creating a unique numeric month 'serial' that you can use instead.

     - there is no error trapping in the formula to deal with alternative Pivot structures (look into stuff like HASONEVALUE() and ISFILTERED()).

    Hope this helps - its untested but should set you down the right path.

    Jacob


    • Edited by Jacob Barnett Friday, March 15, 2013 1:30 AM
    • Marked as answer by tanrou Friday, March 15, 2013 3:16 PM
    Friday, March 15, 2013 1:29 AM

All replies

  • Tanrou, without more data its a little difficult to test but your formula should look something like this:

    =CALCULATE([MM2013],
        ALL('MM2013'[Month]),
            FILTER(ALL('MM2013'[Month]),'MM2013'[Month]<=max('MM2013'[Month]))
               )
       

    This is based on Rob Collie's 'Greatest Formula in the World' (google it, I can't add links) and basically uses the FILTER() to create a context where the original month context is opened out using ALL() and it then just queries rows from the table where the month is less than or equal to the context month in your Pivot. The explanation of this technique in Rob's book is worth the price alone!!

    A couple of caveats:

     - My assumption is that you aren't using a separate date table (although you should be).

     - if your data has mulitple years that will be problematic, suggest creating a unique numeric month 'serial' that you can use instead.

     - there is no error trapping in the formula to deal with alternative Pivot structures (look into stuff like HASONEVALUE() and ISFILTERED()).

    Hope this helps - its untested but should set you down the right path.

    Jacob


    • Edited by Jacob Barnett Friday, March 15, 2013 1:30 AM
    • Marked as answer by tanrou Friday, March 15, 2013 3:16 PM
    Friday, March 15, 2013 1:29 AM
  • That works Jacob!  That gets me a running distinct count of account numbers by month, as yes my data is only 1 year's worth.
    Friday, March 15, 2013 3:16 PM