Forum Discussion

gussie's avatar
gussie
Frequent Visitor
4 years ago
Solved

Line and Clustered Column Chart Help - Negative 100%

Hey

 

I've got a Line and Clustered Column Chart that is showing valuable information, but it is also showing things that a irrelevant. The green line is telling me the Year over Year percentage change from this year and last year.

I only what to show where the percentage change isn't comparing things without data or a ZERO value... 

Can anyone help here with the design?

 

Thanks 🙂

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi gussie ,

     

    Based on my test, you could use below measure for Line value:

    Measure = IF(ABS([YoY Percent Change])<>1,[YoY Percent Change],BLANK())

    Or add <>BLANK() && <>0  in your YOY change measure like this:

    YoY Percent Change 2 = 
VAR _LastYear =CALCULATE( SUM('Table'[Values]), FILTER('Table',[Date].[Year]=2020))
VAR _ThisYear =CALCULATE( SUM('Table'[Values]), FILTER('Table',[Date].[Year]=2021))
RETURN   IF(_LastYear<>BLANK() && _LastYear<>0 && _ThisYear<>BLANK()&& _ThisYear<>0 , DIVIDE(_LastYear, _ThisYear,0)-1 ,BLANK())

     

     

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

4 Replies

    • gussie's avatar
      gussie
      Frequent Visitor
      4 years ago

      Thanks Farhan, 

      I've tried the following ...


      is_before_today = IF(dim_date[actual_date] <= TODAY(), True, False)

      as a fitler on the visual but this does nothing... 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi gussie ,

     

    Based on my test, you could use below measure for Line value:

    Measure = IF(ABS([YoY Percent Change])<>1,[YoY Percent Change],BLANK())

    Or add <>BLANK() && <>0  in your YOY change measure like this:

    YoY Percent Change 2 = 
VAR _LastYear =CALCULATE( SUM('Table'[Values]), FILTER('Table',[Date].[Year]=2020))
VAR _ThisYear =CALCULATE( SUM('Table'[Values]), FILTER('Table',[Date].[Year]=2021))
RETURN   IF(_LastYear<>BLANK() && _LastYear<>0 && _ThisYear<>BLANK()&& _ThisYear<>0 , DIVIDE(_LastYear, _ThisYear,0)-1 ,BLANK())

     

     

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