Forum Discussion

erihsehc's avatar
erihsehc
Helper III
8 years ago
Solved

Waterfall issue

hi there,   I am not sure how to create waterfall by using the different measures. Would expert please help? Attached the pbix, I would like the waterfall update when slicers selection of "custome...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi erihsehc,

     

    You could try two ways.

     

    Solution 1

    Create a one to many relationship between 'Sheet1' and 'Sheet2' based on common field.

     

    Create a simple measure like below. Add this measure into waterfall chart rather than a calculated column.

    Measure amount = SUM(Sheet2[amount])

     

    Solution2

    Without any relationship between 'Sheet1' and 'Sheet2', create a measure like this, also, add this measure into waterfall chart.

    Measure Amount2 =
IF (
    LASTNONBLANK ( Sheet1[Column1], 1 ) = "PAT Budget",
    [PAT budget],
    IF (
        LASTNONBLANK ( Sheet1[Column1], 1 ) = "Fty PBT",
        [Fty PBT],
        IF (
            LASTNONBLANK ( Sheet1[Column1], 1 ) = "SGA & Others",
            [SGA],
            IF ( LASTNONBLANK ( Sheet1[Column1], 1 ) = "Tax", [Tax] )
        )
    )
)

     

    I have uploaded the modified .pbix file for your reference.

     

    Best regards,
    Yuliana Gu

erihsehc's avatar
erihsehc
Helper III
8 years ago

hi @v-yulgu-msft, yes it is, the waterfall should update based on the slicer selecion. here is the file pbix, thanksMan Happy

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
8 years ago

Hi erihsehc,

 

You could try two ways.

 

Solution 1

Create a one to many relationship between 'Sheet1' and 'Sheet2' based on common field.

 

Create a simple measure like below. Add this measure into waterfall chart rather than a calculated column.

Measure amount = SUM(Sheet2[amount])

 

Solution2

Without any relationship between 'Sheet1' and 'Sheet2', create a measure like this, also, add this measure into waterfall chart.

Measure Amount2 =
IF (
    LASTNONBLANK ( Sheet1[Column1], 1 ) = "PAT Budget",
    [PAT budget],
    IF (
        LASTNONBLANK ( Sheet1[Column1], 1 ) = "Fty PBT",
        [Fty PBT],
        IF (
            LASTNONBLANK ( Sheet1[Column1], 1 ) = "SGA & Others",
            [SGA],
            IF ( LASTNONBLANK ( Sheet1[Column1], 1 ) = "Tax", [Tax] )
        )
    )
)

 

I have uploaded the modified .pbix file for your reference.

 

Best regards,
Yuliana Gu

  • erihsehc's avatar
    erihsehc
    Helper III
    8 years ago

    thanks a lot v-yulgu-msft, this perfectly works. BTW, why using lastnonblank function works? it is amazing. (I am a beginner on that):smileyhappy:

    • v-yulgu-msft's avatar
      v-yulgu-msft
      Microsoft Employee
      8 years ago

      Hi erihsehc,

       

      In your scenario, you created a calculated column which is static. To show values conditionally based on slicer selection, we should choose measure. But, in a measure, we cannot directly reference to a column name, we can use lastnonblank function to get corresponding value in current context (current row). If Column1 is numeric, you can also use MAX, MIN, instead of LASTNONBLANK.

       

      Best regards,
      Yuliana Gu