Displaying "0" in table or matrix

[Dehlilah]

Hello, 

I created a code to sum sub-categories and the code works. However, I want to visualize areas that have zero as a value as this is important to know regions without. 

Here is my code: 

Remaining =

VAR Total =

    CALCULATE(

        SUM('Table'[Column1]),

        FILTER(

            'Table',

            'Table'[Column 2] = "x1" ||

            'Table'[Column 2] = "x2" ||

            'Table'[Column 2] = "x3"

        )

    )

RETURN

    IF(

        ISBLANK(Total),

        0,

        Total

    )

I have tried adding "<>0" or "+0" after the paranthesis, using the "show items with no data" option next to the variable and I have tried a different code: 

Remaining =

COALESCE(

CALCULATE(

        SUM('Table'[Column1]),

        FILTER(

            'Table',

            'Table'[Column 2] = "x1" ||

            'Table'[Column 2] = "x2" ||

            'Table'[Column 2] = "x3"

        )

    )

,

    0

)

Can anyone offer a change to help visualize all the data? 

[v-shex-msft · Accepted solution]

HI @Dehlilah,

In fact, this should be divided to two scenarios to handle your requirement.
If these records real include in your table, you can simply write a Dax expression and use if statement to check and replace them.
If these blanks case with table records missing, you may need to create a unconncted new table(as lbendlin said)with whole category values to use on matrix column field. Then you can extract the current table row and new table column value as condition to lookup records and return default value if this row/column value group does not include in your table.

Regards,

Xiaoxin Sheng

[lbendlin]

To report on things that are not there you need to use disconnected tables and/or crossjoins