Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
kang990
Frequent Visitor

Summarize table with zero

Hello,

I have two tables as below:

 

Sales

SalespersonDateCategory
AAA2023-01-02BAT
BBB2023-03-03TGA
AAA2023-03-03TGA
BBB2023-03-03TGA
CCC2023-04-23BAT
CCC2023-04-23TGA

 

Salespersons

NameDept
AAA01
BBB01
CCC04

 

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.

DateSalespersonCategoryCount
2023-01-02AAABAT1
2023-01-02AAATGA0
2023-01-02BBBBAT0
2023-01-02BBBTGA0
2023-01-02CCCBAT0
2023-01-02CCCTGA0
2023-03-03AAABAT0
2023-03-03AAATGA1
2023-03-03BBBBAT0
2023-03-03BBBTGA2
2023-03-03CCCBAT0
2023-03-03CCCTGA0
2023-04-23AAABAT0
2023-04-23AAATGA0
2023-04-23BBBBAT0
2023-04-23BBBTGA0
2023-04-23CCCBAT1
2023-04-23CCCTGA1

 

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!

4 REPLIES 4
Anonymous
Not applicable

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
Super User
Super User

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

ThxAlot_0-1694920859690.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LearnAndPractise(Everyday)


)



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

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

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.