Power Pivot - calculating distinctcount per week (rather than per day)

Question

I am having problems with a distinctcount calculated by week. I have the pivot table below. I want to calculate the distinct number of vendors that have sold more than $2400 per week.

I have the following data table "sales" (only the first rows, but it has several vendors and other weeks as well):

sales day   sales week  vendor ID   Total Sales
02.11.2020        45    vendor 1    405
03.11.2020        45    vendor 1    464
04.11.2020        45    vendor 1    466
05.11.2020        45    vendor 1    358
06.11.2020        45    vendor 1    420
07.11.2020        45    vendor 1    343

I have tried to calculate it as such:

= [vendor] =distinctcount('Sales'[vendor ID])

= [Total_sales] = sum('Sales'[Total Sales])

= [# vendors - 2400] =calculate([vendor],filter('Sales',[Total_sales]>2400))

I know that this calculation considers the sales per day, not per week. so, if instead of using $2400 I used $300, for instance, then both vendors would be marked, since in at least one day, the sales of both are higher than $300. But I only want to consider the sales in a weekly basis.

What I expect (check pivot table below): Vendor 2 would be marked (sales = 2456), but not vendor 1 (sales = 1341), i.e., total number of vendors = 1. However, none of the vendors are being counted, since no daily sales are higher then $2400

Row Labels  # Vendors (distinct)    total sales
Store A                                3797
week 45                                3797
Vendor 1                               1341
02.11.2020                              348
04.11.2020                              202
05.11.2020                              335
06.11.2020                              308
07.11.2020                              148
Vendor 2                               2456
02.11.2020                              405
03.11.2020                              464
04.11.2020                              466
05.11.2020                              358
06.11.2020                              420
07.11.2020                              343

I also tried to create a column of sales in which I removed the day filter, like this:

=calculate([total_sales],ALL('sales'[sales day]))

and then recalculated the [# vendors - 2400], but it still gets me the same result as above.

The question is: how do I get to consider the total sales value per week (and not per day) for the distinctcount. Thank you for the help!

Puno Puno · Accepted Answer · 2021-01-01T13:23:51

Do you have a Date calendar in your file? if no try to make one, then have a relationship from date to sales day (assuming this has your dates). That way you should be able to summarize by any date grouping eg, Month, Day, Week, Quarter etc...Or you can try parsing the other date field and add new columns to your table = weeknum(Tablename[sales day])

Power Pivot - calculating distinctcount per week (rather than per day)

1 Answers