Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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!
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
)
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?
User | Count |
---|---|
14 | |
9 | |
7 | |
7 | |
6 |
User | Count |
---|---|
21 | |
11 | |
10 | |
10 | |
8 |