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

Helper I

## Matrix to show zero or blank value when row value is missing

dear gurus

I have a situation where a matrix (or table) does not show all row combination if an entry is missing.

I have a simple table of sales data with Customer, Month and Quantity that represents the sales. I want to compare the sales that each customer makes in the current month vs a previous month. My formulas are below reported and they work in a good way, except if the customer is missing as made no sales in the previous month.

If customer is missing in the current month or in the previous month, I would like nonetheless to see that he made no sales vs. positive quantity in the other month.

I calculate the total sales made in the Current Month

Calculate(
SUM ('Demand Database'[Quantity]),
'Demand Database'[Demand Sub]="Demand CM"
),

I calculate the total sales made in a specific month I select:
Calculate(
SUM('Demand Database'[Quantity]),
'Demand Database'[Demand Sub]=selectedvalue('Demand Database'[Demand Sub])

in the matrix, I activated the option "show items with no value" but it doesn't work.

any suggestion is highly welcome!

1 ACCEPTED SOLUTION
Super User

Hello @moia79,

1. Create a separate Date table. This table should include all dates for the relevant period and not just the dates present in your sales data. Next, create a Master Customer table that lists all customers.

2. Set relationships between your sales data table ('Demand Database') and the newly created Date and Customer tables.

3. Modify your DAX measures as follows, then adjust the Matrix Visual:

``````Total Sales Current Month =
CALCULATE(
SUM('Demand Database'[Quantity]),
'Demand Database'[Demand Sub] = "Demand CM",
ALL('Date')  // Replace 'Date' with your Date table name
)
``````
``````Total Sales Selected Month =
CALCULATE(
SUM('Demand Database'[Quantity]),
'Demand Database'[Demand Sub] = SELECTEDVALUE('Demand Database'[Demand Sub]),
ALL('Date')  // Replace 'Date' with your Date table name
)
``````

Should you require any further assistance, please do not hesitate to reach out to me.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
2 REPLIES 2
Helper I

it looks that adding "ALL(Date)" works - I have already a date table linked with my database. I tried several other "ALL" combinations but none was working correctly.

Thanks

Super User

Hello @moia79,

1. Create a separate Date table. This table should include all dates for the relevant period and not just the dates present in your sales data. Next, create a Master Customer table that lists all customers.

2. Set relationships between your sales data table ('Demand Database') and the newly created Date and Customer tables.

3. Modify your DAX measures as follows, then adjust the Matrix Visual:

``````Total Sales Current Month =
CALCULATE(
SUM('Demand Database'[Quantity]),
'Demand Database'[Demand Sub] = "Demand CM",
ALL('Date')  // Replace 'Date' with your Date table name
)
``````
``````Total Sales Selected Month =
CALCULATE(
SUM('Demand Database'[Quantity]),
'Demand Database'[Demand Sub] = SELECTEDVALUE('Demand Database'[Demand Sub]),
ALL('Date')  // Replace 'Date' with your Date table name
)
``````

Should you require any further assistance, please do not hesitate to reach out to me.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors