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.

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Hi Anonymous 

please try

 

Cumulative Base =
VAR SelectedData =
    ALLSELECTED ( 'FY Chart Data' )
VAR CurrentRank =
    MAX ( 'FY Chart Data'[Rank] )
VAR PreviousData =
    FILTER (
        SelectedData,
        'FY Chart Data'[Rank] <= CurrentRank
            && NOT ( 'FY Chart Data'[category] IN { "Bud", "Fcst" } )
    )
RETURN
    CALCULATE ( [CV €M], PreviousData )