Monthly and Weekly Distinct Count

[rickster97]

Hi,

I want to calculate unique customers / week, such that, when I add them up I get the same number of unique customers / month.

I have the following PowerPivot table:

Total customers (took distinct count of customer ID) per month were:
1) January (ene): 1
2) February (feb): 28
3) March (mar): 219
4) April (abr): 416

I actually made a Week Column (Columna 1) in the original database but it still doesn't work the way I need it to (red numbers are the sum of all weeks for a given month):

Can anyone help please?

 

[pjmorris]

The problem you're suffering from is the way Pivot tables calculate their values. Each value is independent of the others around it. To calculate any particular value one step is to define the 'filter context' of the cell. So for Feb, Semana 3 as an example. It will calculate the Distinct Customer IDs in Semana 3 of Feb, in this case 7. To Calculate the Subtotal the filter context is only Feb (ie all weeks in the month) and it finds only 28 distinct values. The reason you anticipate 30 is because some customers were present in more than one week in the month so they appear multiple times across the weeks that comprise Feb.

I believe there may be ways around this, but it can be very frustrating. Google 'incorrect totals in PowerPivot'.

HTH

 

[rickster97]

The problem you're suffering from is the way Pivot tables calculate their values. Each value is independent of the others around it. To calculate any particular value one step is to define the 'filter context' of the cell. So for Feb, Semana 3 as an example. It will calculate the Distinct Customer IDs in Semana 3 of Feb, in this case 7. To Calculate the Subtotal the filter context is only Feb (ie all weeks in the month) and it finds only 28 distinct values. The reason you anticipate 30 is because some customers were present in more than one week in the month so they appear multiple times across the weeks that comprise Feb.

I believe there may be ways around this, but it can be very frustrating. Google 'incorrect totals in PowerPivot'.

HTH

I understand that I get a total of 30 for February because there were 30 purchases made by 28 clients.
What I would like to be able to get sort of "automatically" is the following:

Total Unique Clients February:
Week 1: 5
Week 2: 9
Week 3: 6
Week 4: 8
Total: 28

I actually figured a manual 4-step approach to get weekly results but it is not practical given the amount of data I have to analyze.
Contrary to most videos I don't want to delete the total or make the total 30 (February). I want the total to remain 28 (February), so that each week only includes customers that made their first purchase of the month during that week.

Thanks in advance.

 

[pjmorris]

I feel sure its possible, but it will require a fairly complex 'measure'. I would recommend moving this thread to the 'Powertools' forum as there is some truly inspirational Powerquery/Powerpivot knowledge there.

 

[rickster97]

I feel sure its possible, but it will require a fairly complex 'measure'. I would recommend moving this thread to the 'Powertools' forum as there is some truly inspirational Powerquery/Powerpivot knowledge there.

Thank you. I have posted this thread in the "Powertools" forum now.

 

[rickster97]

Hi,

I want to calculate unique customers / week, such that, when I add them up I get the same number of unique customers / month.

I have the following PowerPivot table:
View attachment 64481
Total customers (took distinct count of customer ID) per month were:
1) January (ene): 1
2) February (feb): 28
3) March (mar): 219
4) April (abr): 416

I actually made a Week Column (Columna 1) in the original database but it still doesn't work the way I need it to (red numbers are the sum of all weeks for a given month):
View attachment 64482

Can anyone help please?

I have tried using PowerBI but still can't figure it out.
Does this seem like a solvable problem?

 

[pjmorris]

Measures in PowerBI or PowerPivot rely on the same 'engine'. I think its possible, but I also think it'll be quite a complex measure. Sorry I cant be more help.

Regards