Matrix visual to show rows without values

[ErikHolmberg]

Hi,

I'm trying to create a matrix visual that shows all customers even if they don't have any sales figures. And I want it to work with a date slicer. The vision I'm trying to reach is something like 

Client ID	Business area 1	Business area 2	Business area 3	Total
Client 1	0	100	0	100
Client 2	100	200	0	300
Client 3	0	0	0	0
Client 4	100	100	100	300
Client 5	0	0	0	0
...	...	...	...	...

I have two data tables that I'm using for this, the first one is with sales figures. Each row represents a deal so that means that every customer might not pe represented in this table, especially when using a date slicer on the visual. The sales table looks like this 

Client ID	Amount	Date	Business area
Client 1	100	2024/12/15	Business area 2
Client 2	100	2024/11/27	Business area 1
Client 2	200	2025/01/15	Business area 2
Client 4	100	2025/01/06	Business area 1
Client 4	100	2024/12/08	Business area 2
Client 4	100	2024/01/11	Business area 3
...	...	...	...

I also have a table that includes all customers regardless of when their bought any products, it looks something like this 

Client ID	Date created
Client 1	2024/10/23
Client 2	2024/11/27
Client 3	2024/01/12
Client 4	2024/01/11
Client 5	2024/11/30
...	...

Any ideas how to get this right?

Thanks a bunch for any help!

[Anonymous]

Hi, @ErikHolmberg 

May I ask if you have gotten this issue resolved? If it is solved, please share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

If it is not resolved, I hope you will provide the full .pbix file via OneDrive or SharePoint. Please be careful to remove all sensitive information and we will do our best to provide ideas for your issue.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[danextian]

Hi @ErikHolmberg 

The formula is as simple as

[Total Sales] + 0

But make sure you use  the column Client dimension table as if you use the one from your fact table and there isn't a row for that client within the range selected, that client will not appear even if your rewrite your formula to return 0 if sales value is blank.

[bhanu_gautam]

@ErikHolmberg First create a relationship between the Client ID in the customer table and the Client ID in the sales table.

Create a measure to sum the sales amounts

Total Sales = SUM(Sales[Amount])

To ensure that all customers are displayed even if they don't have sales, you need to modify the measure to handle cases where there are no sales.

DAX
Total Sales with Zero =
IF(
ISBLANK([Total Sales]),
0,
[Total Sales]
)

Use this measure in matrix

[ErikHolmberg]

Thanks for the quick answer!

What I'm not quite getting is how I with this will be able to divide the the sales per Business Area, sorry if that wasn't clear in the original post