Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Movement in Water fall chart Help

Hi, I have two quarters data.

 

2015 Q 4 and 2016 Q1 data.

The sales is 1000 dollars in Q4. and the 2016 Q1 data also shows 1000 dollars which basically encompasses the 1000 dollars of 2015 Q4.

 

I want a water fall chart which shows 1000 dollars in the first, then 0 dollars for the 2016 Q1 and the total as 1000 dollars.

When i apply the water fall chart it shows 1000 dollars as 1st, 10000 for second and 2000 as total which doesn't suit the movement logic.

 

Any help will be appreciated.

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    Based on my test, I could only do this:

    Measure = 
VAR _curr =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( 'Table', 'Table'[YearQuartre] = MAX ( 'Table'[YearQuartre] ) )
    )
VAR _pre =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[YearQuartre]
                = MAXX (
                    FILTER ( ALL ( 'Table' ), [Date] < MAX ( 'Table'[Date] ) ),
                    [YearQuartre]
                )
        )
    )
RETURN
    IF (
        HASONEVALUE ( 'Table'[YearQuartre] ),
        _curr - _pre,
        SUM ( 'Table'[Value] )
    )

     

    In this case, I suggest you import Simple Waterfall or Ultimate Waterfall Free 4.4.6 or Waterfall Cahrt by Visual BI visual like this:

    Measure 2 = IF(MAX('Table'[YearQuartre])= MINX(ALL('Table'),[YearQuartre]),0,SUM('Table'[Value]))

     

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    Based on my test, I could only do this:

    Measure = 
VAR _curr =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( 'Table', 'Table'[YearQuartre] = MAX ( 'Table'[YearQuartre] ) )
    )
VAR _pre =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[YearQuartre]
                = MAXX (
                    FILTER ( ALL ( 'Table' ), [Date] < MAX ( 'Table'[Date] ) ),
                    [YearQuartre]
                )
        )
    )
RETURN
    IF (
        HASONEVALUE ( 'Table'[YearQuartre] ),
        _curr - _pre,
        SUM ( 'Table'[Value] )
    )

     

    In this case, I suggest you import Simple Waterfall or Ultimate Waterfall Free 4.4.6 or Waterfall Cahrt by Visual BI visual like this:

    Measure 2 = IF(MAX('Table'[YearQuartre])= MINX(ALL('Table'),[YearQuartre]),0,SUM('Table'[Value]))

     

    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.