Issue with Matrix data to display

Hi Krutikgawale33​,

you can give it a try with this DAX which fills the empty ones with 0.

DisplayValue = COALESCE(SUM(Table[YourNumericColumn]), 0)

Hope this helps, if so consider leaving kudos or mark it as solution.
Best regards!

Hi Krutikgawale33​ 

Have you had a chance to look through the responses shared earlier? If anything is still unclear, we’ll be happy to provide additional support.

Hi Krutikgawale33​,

I think the 659 is actually showing you a data-model issue rather than a Matrix issue.

Looking at your sample, Table2 contains these Project IDs that do not exist in Table1:

hgi = 9
srs = 75
lkl = 575

Those add up to 659.

Because those Project IDs have no matching row in Table1, Power BI groups them under the blank/unknown Project member.

That is why you can see 659 against the blank Project row.

The important part is that Power BI cannot automatically place that 659 under “Next Yr”, because there is no matching Table1 row that tells the model:

hgi -> Next Yr
srs -> Next Yr
lkl -> Next Yr

So I would fix this in the model rather than with COALESCE.

If your business rule is that any Project ID missing from Table1 should belong to “Next Yr”, add those missing Project IDs to your Project/date mapping table and assign Date(Y-M) = “Next Yr”.

Then your dimension would effectively contain:

xyz -> 26-Feb
abc -> 26-Mar
gug -> 26-Jul
jlj -> 26-Aug
hohg -> Next Yr
yuigf -> Next Yr
hgi -> Next Yr
srs -> Next Yr
lkl -> Next Yr

After that, keep the normal one-to-many relationship from the Project table to Table2 and use the Project/date fields from that dimension in the Matrix.

Your existing measure can then simply be:

Total Value =
SUM ( Table2[values] )

and the 659 will naturally appear under Next Yr.

Microsoft explains in the VALUES documentation that Power BI can create a blank member when related tables contain unmatched keys. That is effectively what you are seeing here.

I would avoid using COALESCE for this particular requirement because COALESCE only replaces a blank result with another value; it does not establish the missing Project -> Next Yr relationship.

If those three projects genuinely belong to Next Yr, fixing the mapping table is the cleaner and more reliable solution.

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

Hi,

PBI file attached.

Hope this helps.

Hi Krutikgawale33​ 

Following up to confirm if the earlier responses addressed your query. If not, please share your questions and we’ll assist further.