How to count number of sales per client and accumulated sales per client

[ultimathor]

Hi,

I have a normal sales table that includes transaction number, date, amount, user-email and a bunch of other columns. I want to calculate two more columns in PP.

The first column should count how many total purchases have been made by the particular user-email of that row. The reason is that I want to make an analysis across different countries to see if there is a difference in number of sales per user.

The second column should count how many purchases have been made by the particular user email of that row up until and including this particular sale.

I am sure the answer is quite simple but as I'm new to PP I can't seem to figure it out. Suggestions highly appreciated .

Thor

 

[ca.moazzam]

Hi you need to use SUMIF function for this. In your first scenario, Range would be the email addresses column (first row to last row), Criteria would be a particular Email Address and Sum Range would be the Sales column. In your seond scenario you either need to use SUMIFS or IF and SUM combination. In case of a problem email me your file at ca.moazzam@gmail.com.

Moazzam

 

[ultimathor]

Hi Moazzam,

Thanks for your tip. That was the way I had solved the problem in my regular pivot table and that worked fine.

However, what I'm looking for is a way to accomplish the same in PowerPivot.

Do you have a suggestion measure of calculate formula?

Thanks,

Thor

 

[ultimathor]

Hi you need to use SUMIF function for this. In your first scenario, Range would be the email addresses column (first row to last row), Criteria would be a particular Email Address and Sum Range would be the Sales column. In your seond scenario you either need to use SUMIFS or IF and SUM combination. In case of a problem email me your file at ca.moazzam@gmail.com.

Moazzam

Hi Moazzam,

Thanks for your tip. That was the way I had solved the problem in my regular pivot table and that worked fine.

However, what I'm looking for is a way to accomplish the same in PowerPivot.

Do you have a suggestion measure of calculate formula?

Thanks,

Thor

 

[ca.moazzam]

I haven't used PP much. Will have a look and let u know provided anybody else doesn't do earlier.

 

[marco.russo]

I'm not sure whether you want to use measures or calculated columns.
The measures would be:

UserTotalSales := 
    CALCULATE ( 
    COUNTROWS ( Sales ),
    ALLEXCEPT( Sales, Sales[user-email] )
)
UserRunningSales := 
CALCULATE ( 
        COUNTROWS ( Sales ),
        ALLEXCEPT( Sales, Sales[user-email] ),
        FILTER( 
                ALL ( Sales[date] ),
                Sales[date] <= MAX( Sales[date )
        )
)

The calculated columns would be:

UserTotalSales = 
COUNTROWS( 
        FILTER( 
                Sales, 
                Sales[user-email] = EARLIER ( Sales[user-email] ) 
        )
)
       
UserRunningSales = 
COUNTROWS( 
        FILTER( 
                Sales, 
                Sales[user-email] = EARLIER ( Sales[user-email] ) 
                && Sales[date] <= EARLIER( Sales[date )
        )
)

 

[Laurent C]

@marco: wouldn't the pattern for measures also work for calculated columns? do I overlook anything, or do you use a different pattern for other reason (circualar reference, didactical, ...)?

 

[marco.russo]

Mainly didactical, but circular reference and the need of adapting the code could be another one. The existing measures assume you have a filter context available for the filter expressions, in case you use the same expression of a measure in a calculated column, you have to consider that second and following arguments of the CALCULATE statement have an empty filter context and the row context is still not translated in a filter context. So you should either wrap the entire expression in a CALCULATE (or write "= [measure]" after defining the measure) or change the filter statements to make use of the existing row context.

 

[Laurent C]

As far as I am concerned, your explanation is crystal clear ... To sum it up: MAX( Sales[Date]) would be evaluated for the whole Sales table, so that Sales[date] <= MAX( Sales[date )
would always be true and would not filter anything.

 

[marco.russo]

Correct - you should write EARLIER( Sales[Date] ) instead of MAX( Sales[Date] ) to avoid the issue - but I'm not sure it's enough, we should check the remaining arguments. A safer way is wrapping the entire expression in another CALCULATE statement - but in case of a calculated column using the FILTER approach is not bad (also for readability), unless you have so many rows that would increase processing time too much.