Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Line Chart: Not showing Line when Zero

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

 

Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    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's avatar
    mussaenda
    6 years ago

    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's avatar
    Anonymous
    6 years ago

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

10 Replies

  • AnkitBI's avatar
    AnkitBI
    Solution Sage
    6 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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.
      • v-xuding-msft's avatar
        v-xuding-msft
        Community Support
        6 years ago

        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.

  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    6 years ago

    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.