cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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 13K+ 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 13K+ 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

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors