8 Replies Latest reply on Oct 14, 2016 11:33 AM by Francesca Cristoforetti

# Calculate percent of total when using a measure defined as WINDOW_SUM

Hi all,

I have defined a calculated field as:

WINDOW_SUM(

COUNTD(

IF [date]={FIXED [customer_id] : MIN([date])}

THEN [customer_id]

END),FIRST(),0)

I also have defined another calculated field ([Customer Bands]) wich define three categories of customers.

I have a simple woorksheet showing month of date and the running count of distinct customer for each Customer Band and the total, please see the attached screenshot

I would like to calculate, for each month the percent of total for each customer band. it seems to be a simple things but I don't know how to do it.

Any suggestion or idea?

Thanks

• ###### 2. Re: Calculate percent of total when using a measure defined as WINDOW_SUM

Sorry, I don't understand what you mean. [Customer Bands]  is a discrete field which assume values L, M or H. My window sum calculation is called [Running Customer Count] and it obsiously takes numeric integer values.

• ###### 3. Re: Calculate percent of total when using a measure defined as WINDOW_SUM

Oh.  I thought [Customer Bands] was the calc that was putting out 6, 9, 22 for November 2011.

So whatever field you are using to display the values.  That's what you want in the numerator.

• ###### 4. Re: Calculate percent of total when using a measure defined as WINDOW_SUM

I have attacched a woorkbook in order to better explain my situation. As you can see in Sheet1 I have calculated for each month che distinct customer count in each group (L, M or H) and the percent of total for each row. I would like to do the same with running count (Running Customer Count field) and I still don't know how do this. Sorry, but I don't unterstand how apply what you write in your answer, Joe.

Now I have also understand that I did an error in defining my Customer Bands. In my idea the groups are based on the values of two metrics: order count and average order amout for each customer. I would like to define also this measures as cumulated values, from the beginning to the current month, but I can't understand how I should do. In my workbook I have defined them at fixed level of detail ([customer_id]), which means that I'm using the current classification of my customers, including all orders. the two field I'm currently using are Average Amount (customer level) and Order Count (customer level).

I have tried something like this:

{ FIXED [customer_id] : RUNNING_SUM( COUNTD([order_id]) ) }

and

{ FIXED [customer_id] : RUNNING_SUM( SUM([amount]) ) }

But an error says that "Level of detail expression cannot contain table calculation or the ATTR function"

As you can see in Sheet2, the customer band associated with a customer doesn't change in time because it's the actual classification.

Any other ideas?

PS: should I open another thread for this second question?

• ###### 5. Re: Calculate percent of total when using a measure defined as WINDOW_SUM

Let's take one step at a time.

You have three Bands:  H, L, M.  Are the Running sums correct?  Even before that, arte the Running Customer Count and Distinct Count of Customer ID correct?

There is an inherent problem with trying to do a running COUNTD.  If you go month-by-month, your monthly COUNTD will get the distinct IDs within that month.  A running COUNTD will just sum up the month-by-month totals.  But if a customer bought something in January and also bought something in March, he will be counted in the COUNTD for each of those months, and therefore he will be counted twice in the running total.  Maybe you want it to be counted that way, but at least be aware of this concern.  (And there is not an easy way to address that in Tableau.)

Once we have the various counts correct, then we can work at creating percentages.  Can you clarify that the numbers on Sheet 1 are correct?

• ###### 6. Re: Calculate percent of total when using a measure defined as WINDOW_SUM

Hi Joe

using this calculated field

WINDOW_SUM(

COUNTD(

IF [date]={FIXED [customer_id] : MIN([date])}

THEN [customer_id]

END),FIRST(),0)

I'm counting each customer just on his first order, from the beginning until the current month. te calculated field seems to be right.

My problem now is how shoul I calculate the three bands, as I explained in my previuos post (is it clear?) and then how should I calculate the percentage.

• ###### 7. Re: Calculate percent of total when using a measure defined as WINDOW_SUM

See attached.

I made an extra table calc that sums up the total.  I didn't realize that the total you are showing on your sheet was tableau-generated.  So make your own total, and then do the math.  Note that the table calc for the total runs along [Bands] and the percentage calc runs along months.

• ###### 8. Re: Calculate percent of total when using a measure defined as WINDOW_SUM

Thank you for your answer Joe, It seems to be what I was looking for.

Now I'm going to solve my problem about the calculation of [Customer Bands].