Line Chart: Not showing Line when Zero

[Anonymous]

Hi. This is my first post.

I am trying to build the a combo bar and line chart. How do I NOT show the line that is Zero? Please see picture below. I want to stop the red line at Sep and don't show the zeros.

Thanks

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

It caused by brackets. Try this.

Actual Cumulative Adj =
IF (
    CALCULATE (
        SUM ( 'Fact Summary'[YTD Actuals] ),
        FILTER (
            ALLSELECTED ( 'DimDate'[FIN_YR_MONTH] ),
            ISONORAFTER ( 'DimDate'[FIN_YR_MONTH], MAX ( 'DimDate'[FIN_YR_MONTH] ), DESC )
        )
    ) = 0,
    BLANK (),
    CALCULATE (
        SUM ( 'Fact Summary'[YTD Actuals] ),
        FILTER (
            ALLSELECTED ( 'DimDate'[FIN_YR_MONTH] ),
            ISONORAFTER ( 'DimDate'[FIN_YR_MONTH], MAX ( 'DimDate'[FIN_YR_MONTH] ), DESC )
        )
    )
)

Best Regards,

Xue Ding

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

[mussaenda · Accepted solution]

Hi @Anonymous ,

Try putting your previous formula in VAR

Actual Cumulative Adj =
VAR  ActualCumAdj =  
   CALCULATE(
       SUM('Fact Summary'[YTD Actuals]),
           FILTER(
               ALLSELECTED('DimDate'[FIN_YR_MONTH]),
               ISONORAFTER('DimDate'[FIN_YR_MONTH],         
                      MAX('DimDate'[FIN_YR_MONTH]),
                      DESC)
          )
)

RETURN 

if(  ActualCumAdj =  0, BLANK(),  ActualCumAdj)

also, the reason of the error is that you missed one ")".

You missed to close the calculate code before putting =0.

You can easily trace it if you will organise your formula with correct spaces and br

[v-xuding-msft]

Hi @Anonymous ,

As @AnkitBI said, you could create a measure to replace 0 to blank. Then when you put it to chart, it will just show the values without 0. In addition, you also could type a start value in Y-axis line to implement it.

Best Regards,

Xue Ding

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

[AnkitBI]

Please try creating a measure like below. This will return blank, if Line Measure has value 0 else value. Then use this measure in Line Chart.

Measure 5 = if(sum('Table (3)'[Column2]) = 0,blank(),sum('Table (3)'[Column2]))

Thanks
Ankit Jain

Do Mark it as solution if the response resolved your problem. Do like the response if it seems good and helpful.

[netanel]

It really helped, thank you very much!

[Anonymous]

Hi. Thank for your reply, Ankit.

I tried to use the IF formula to the existing formula. My attempt says that there is too few arguement for the IF function. My formula is :

Actual Cumulative Adj =

if(CALCULATE(

    SUM('Fact Summary'[YTD Actuals]),

    FILTER(

        ALLSELECTED('DimDate'[FIN_YR_MONTH]),

        ISONORAFTER('DimDate'[FIN_YR_MONTH], MAX('DimDate'[FIN_YR_MONTH]), DESC)

    )=0, BLANK(),

CALCULATE(

    SUM('Fact Summary'[YTD Actuals]),

    FILTER(

        ALLSELECTED('DimDate'[FIN_YR_MONTH]),

        ISONORAFTER('DimDate'[FIN_YR_MONTH], MAX('DimDate'[FIN_YR_MONTH]), DESC)

    )

)

))

Can you tell what I am doing wrong?

Thanks.

[mussaenda · Accepted solution]

Hi @Anonymous ,

Try putting your previous formula in VAR

Actual Cumulative Adj =
VAR  ActualCumAdj =  
   CALCULATE(
       SUM('Fact Summary'[YTD Actuals]),
           FILTER(
               ALLSELECTED('DimDate'[FIN_YR_MONTH]),
               ISONORAFTER('DimDate'[FIN_YR_MONTH],         
                      MAX('DimDate'[FIN_YR_MONTH]),
                      DESC)
          )
)

RETURN 

if(  ActualCumAdj =  0, BLANK(),  ActualCumAdj)

also, the reason of the error is that you missed one ")".

You missed to close the calculate code before putting =0.

You can easily trace it if you will organise your formula with correct spaces and br

[Anonymous]

Thank you! Your solution works!

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

It caused by brackets. Try this.

Actual Cumulative Adj =
IF (
    CALCULATE (
        SUM ( 'Fact Summary'[YTD Actuals] ),
        FILTER (
            ALLSELECTED ( 'DimDate'[FIN_YR_MONTH] ),
            ISONORAFTER ( 'DimDate'[FIN_YR_MONTH], MAX ( 'DimDate'[FIN_YR_MONTH] ), DESC )
        )
    ) = 0,
    BLANK (),
    CALCULATE (
        SUM ( 'Fact Summary'[YTD Actuals] ),
        FILTER (
            ALLSELECTED ( 'DimDate'[FIN_YR_MONTH] ),
            ISONORAFTER ( 'DimDate'[FIN_YR_MONTH], MAX ( 'DimDate'[FIN_YR_MONTH] ), DESC )
        )
    )
)

Best Regards,

Xue Ding

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

[Anonymous]

Excellent! Thanks a lot

[Anonymous · Accepted solution]

I finally had a chance to try your solution out. It works. Awesome.

[AnkitBI]

@Anonymous . Please confirm if your issue is resolved?

Thanks
Ankit Jain

Do Mark it as solution if the response resolved your problem. Do like the response if it seems good and helpful.