topic Re: Summarize table with zero in DAX Commands and Tips

Summarize table with zero

kang990 — Fri, 15 Sep 2023 05:20:25 GMT

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!

Re: Summarize table with zero

ThxAlot — Sun, 17 Sep 2023 03:19:40 GMT

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

Re: Summarize table with zero

kang990 — Mon, 18 Sep 2023 10:01:56 GMT

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

Re: Summarize table with zero

kang990 — Wed, 20 Sep 2023 05:55:28 GMT

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

Re: Summarize table with zero

Anonymous — Mon, 02 Oct 2023 02:35:41 GMT

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.