Forum Discussion
Summarize table with zero
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?
Any help is greatly appreciated!
4 Replies
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.
Cnt = ADDCOLUMNS( CROSSJOIN( DISTINCT( Sales[Date] ), DISTINCT( Salespersons[Salesperson] ), DISTINCT( Sales[Category] ) ), "Cnt", CALCULATE( COUNTROWS( Sales ) ) + 0 )
