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 "customer" and "division" are changed. thanks

 

I have created the waterfall as below, however it can not be updated by slicer selection

 

pbix

Best regards

ER

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

7 Replies

  • 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

      • 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