Measure has return the 0 where total is there

[NavaneethaRaju]

Hi ,

I created a measure for finding cancelled case count.

1. Cancelled =

        CALCULATE (

    COUNT ( 'Case Detail Fact'[Case Status]),

        'Case Detail Fact'[Case Status] = "Cancelled")

2. Total = COUNT ( 'Case Detail Fact'[Case Status] )

3. Cancel % = [Cancelled]/[Total]

If the cancelled is not done by sales rep, its showing blank there.  instead of blank i have to show 0 and the cancel % should be 0%. I Created a dax IF( ISBLANK([Cancelled],0,[Cancelled]), but it returns 0 in all the places , even the sales rep not done the case on that month.

Only those places alone, i have to show the 0, But while using the isblank its showing all the places. even the total was not there.

Kindly advice.

[Mikelytics]

Hi,

Please put '+0" behind the forst formula and an if statement to have only values where really is something

Cancelled =

IF(
   [Total] <> 0,
     CALCULATE (
        COUNT ( 'Case Detail Fact'[Case Status]),
        'Case Detail Fact'[Case Status] = "Cancelled")
    ) + 0
   ,BLANK()
)

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

[Mikelytics · Accepted solution]

Hi,

there is also another option

you let formula 1 and 2 like it is. formula three you do the following

3. Cancel % = DIVIDE([Cancelled],[Total],0)

in this version you will have BLANK in the first formula when there is no canceled case but you will get 0% as result for formula 3.

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.