Skip to main content
cancel
Showing results for 
Search instead 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

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
v-tangjie-msft
Community Support
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. 

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.LeanAndPractise(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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.