show as 0 instead of Blank for formula

[romovaro]

Hello

I need some help regarding this table. 

I have Product Managers showing num of Projects by month. I have a target and I have the difference between Actual Projects and target by month. The problem I have is that if no Projects...then blank and the formula is not working. I would like to show zero so the table CALCULATES the negative number and shows they are available.

The formula I use for Num Projects with Index column.  (If same customer (CID) has same country and same Go Live Date from previous row is 0, otherwise 1)

NumProjects = IF (

    OR ( OR (

        'PV Cel File'[CID]<>

    CALCULATE (

        MAX ('PV Cel File'[CID]),

        FILTER (

            'PV Cel File',

            'PV Cel File'[Índice] = EARLIER ('PV Cel File'[Índice])-1)

    ),

    'PV Cel File'[Country Long Name]<>

    CALCULATE (

        MAX ('PV Cel File'[Country Long Name]),

        FILTER (

            'PV Cel File',

            'PV Cel File'[Índice] = EARLIER ('PV Cel File'[Índice])-1)

    )

    ),

        'PV Cel File'[GLD].[Date]<>

        CALCULATE(

            MAX('PV Cel File'[GLD].[Date]),

            FILTER(

                'PV Cel File',

            'PV Cel File'[Índice] = EARLIER ('PV Cel File'[Índice])-1)

        )

    )

    ,1

    ,0

    )

and then I have a Target by role table showing ROle, Date and Target.

I have seen different formulas in the forum but none seems to work. I get the same results and same blanks.

1)

Actual-NumProjects = COALESCE( SUM ( 'PV Cel File'[NumProjects] ), 0)

2)

Actual-NumProjects =

IF (

CALCULATE ( SUM ('PV Cel File'[NumProjects] ) ) = BLANK (),

0,

CALCULATE ( SUM ( 'PV Cel File'[NumProjects]  ) )

)

3)

Actual-NumProjects = SUM('PV Cel File'[NumProjects])+0

4)

Actual-NumProjects = VAR Projects =

 SUM ( 'PV Cel File'[NumProjects])

RETURN

 IF (

 ISBLANK ( Projects ),

 0,

 Projects

 )

thanks

[danextian · Accepted solution]

Hi @romovaro 

Do these combinations of row and column values exist in your table? You can't assign a value to a row that doesnt exist.

A similar question was already raised in this post- https://community.fabric.microsoft.com/t5/Desktop/Matrix-showing-Blanks-and-Zeroes/m-p/4304795#M1349...