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

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

Hi erihsehc,

 

How do you want the waterfall chart to be updated? If "customer" and "division" are related to columns you added to waterfall chart, the chart will update automatically based on slicer. 

 

For more advice, please share sample data so that I can test for you.

 

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. pbix, thanks:manhappy:

  • 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: