cannot get distinctcount with calculate and filter at week level

[atroyano]

Hi,

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!

[v-jingzhang · Accepted solution]

Hi @atroyano 

You could create measures like below. Use ALLEXCEPT() to keep the filters on vendor and week but remove the filters on date.

Total Sales Per Week = CALCULATE(SUM(Sales[Total Sales]),ALLEXCEPT(Sales,Sales[sales week],Sales[vendor ID]))

# vendors-2400 = CALCULATE(DISTINCTCOUNT(Sales[vendor ID]),FILTER(Sales,[Total Sales Per Week]>2400))

Then put week column and [# vendor-2400] measure in a table visual to check the result.

Kindly let me know if this helps.

Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

[lbendlin]

What is a week?  Do you have a Dates/Calendar table that identifies your weeks?

Approach the issue step by step (as variables in a measure or via SUMMARIZE)

- get unique list of vendors

- for each vendor get unique list of weeks (based on the mapping of the sales facts to the calendar table)

- for each combination calculate the sales