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

[moia79]

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!

[moia79]

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

[Sahir_Maharaj · Accepted solution]

Hello @moia79,

Can you please try this:

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.