Summarize table with zero

[kang990]

Hello,

I have two tables as below:

Sales

Salesperson	Date	Category
AAA	2023-01-02	BAT
BBB	2023-03-03	TGA
AAA	2023-03-03	TGA
BBB	2023-03-03	TGA
CCC	2023-04-23	BAT
CCC	2023-04-23	TGA

Salespersons

Name	Dept
AAA	01
BBB	01
CCC	04

How can I use SUMMARIZE to count by Date, Category and Salesperson? As not every salesperson has category on that day. I wish to have the result like this, no matter they have sales or not on each day. If no sales, display 0.

Date	Salesperson	Category	Count
2023-01-02	AAA	BAT	1
2023-01-02	AAA	TGA	0
2023-01-02	BBB	BAT	0
2023-01-02	BBB	TGA	0
2023-01-02	CCC	BAT	0
2023-01-02	CCC	TGA	0
2023-03-03	AAA	BAT	0
2023-03-03	AAA	TGA	1
2023-03-03	BBB	BAT	0
2023-03-03	BBB	TGA	2
2023-03-03	CCC	BAT	0
2023-03-03	CCC	TGA	0
2023-04-23	AAA	BAT	0
2023-04-23	AAA	TGA	0
2023-04-23	BBB	BAT	0
2023-04-23	BBB	TGA	0
2023-04-23	CCC	BAT	1
2023-04-23	CCC	TGA	1

With the summarize command I wrote it wouldn't include zero for everyone. How can I solve this issue?

Link to pbix file

Any help is greatly appreciated!

[v-tangjie-msft]

Hi @kang990 ,

Has your problem been solved? If the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. Thanks in advance.

For your follow-up questions, you can create a new post and describe your problem in detail. More users and engineers will be involved to help you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[ThxAlot]

Cnt = 
ADDCOLUMNS(
    CROSSJOIN(
        DISTINCT( Sales[Date] ),
        DISTINCT( Salespersons[Salesperson] ),
        DISTINCT( Sales[Category] )
    ),
    "Cnt", CALCULATE( COUNTROWS( Sales ) ) + 0
)

[kang990]

A follow up question, how can I bring the Dept from Salespersons in to the summarized table?

[kang990]

Hi @ThxAlot , it worked in this way. Thank you for your help! 🙂