cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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 4
Community Support

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.

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

 Expertise = List.Accumulate(        {Days as from Today},        {Skills and Knowledge},        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday))

Frequent Visitor

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

Frequent Visitor

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