Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Running Total with exclusions

Hi all, I am trying to recreate a chart that the business uses currently in Excel, that I would like to move to Power BI. It is like a waterfall chart, but has Targets included in the chart as well ...
Help
  • v-jianboli-msft's avatar
    v-jianboli-msft
    4 years ago

    Hi Anonymous ,

     

    Here is the DAX of these columns:

     

    CV €M 2 =
IF (
    CONTAINSSTRING ( [Category], "Fcst" ) || CONTAINSSTRING ( [Category], "Bud" ),
    0,
    [CV €M]
)

Base 2 =
VAR _a =
    CALCULATE (
        SUM ( Table1[CV €M 2] ),
        FILTER ( Table1, [Index] < EARLIER ( [Index] ) )
    )
RETURN
IF ( ISBLANK ( _a ), 0, _a )

Base 3 =
IF (
    CONTAINSSTRING ( [Category], "Fcst" ) || CONTAINSSTRING ( [Category], "Bud" ),
    0,
    [Base 2]
)

     

    Final output:

     

    Best Regards,

    Jianbo Li

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

v-jianboli-msft's avatar
v-jianboli-msft
Icon for Community Support rankCommunity Support
4 years ago

Hi Anonymous ,

 

Here is the DAX of these columns:

 

CV €M 2 =
IF (
    CONTAINSSTRING ( [Category], "Fcst" ) || CONTAINSSTRING ( [Category], "Bud" ),
    0,
    [CV €M]
)

Base 2 =
VAR _a =
    CALCULATE (
        SUM ( Table1[CV €M 2] ),
        FILTER ( Table1, [Index] < EARLIER ( [Index] ) )
    )
RETURN
IF ( ISBLANK ( _a ), 0, _a )

Base 3 =
IF (
    CONTAINSSTRING ( [Category], "Fcst" ) || CONTAINSSTRING ( [Category], "Bud" ),
    0,
    [Base 2]
)

 

Final output:

 

Best Regards,

Jianbo Li

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