Running Total with exclusions

[Anonymous]

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 (Budget and Forecast) like this:

to do this we have a table like this:

Category	CV €M	Index	CV €M 2	Base 2	Base 3
Q1Sold Churn	1.83	0	1.83	0.00	0.00
Q1 Unsold Churn	4.98	1	4.98	1.83	1.83
Q1 Sold Medium	1.34	2	1.34	6.81	6.81
Q1 Unsold Medium	3.59	3	3.59	8.15	8.15
Fcst Q1	25.22	4	0.00	11.74	0.00
Bud Q1	25.04	5	0.00	11.74	0.00
Q2 Sold Churn	1.83	6	1.83	11.74	11.74
Q2 Unsold Churn	4.98	7	4.98	13.57	13.57
Q2 Sold Medium	1.34	8	1.34	18.55	18.55
Q2 Unsold Medium	3.59	9	3.59	19.89	19.89
Deal 1	8.20	10	8.20	23.48	23.48
Deal 2	6.20	11	6.20	31.68	31.68
Deal 3	5.30	12	5.30	37.88	37.88
H1 YTD Fcst	56.09	13	0.00	43.18	0.00
H1 YTD Bud	70.14	14	0.00	43.18	0.00
Q3 Unsold Churn	6.81	15	6.81	43.18	43.18
Q3 Unsold Medium	4.93	16	4.93	49.99	49.99
Deal 4	17.30	17	17.30	54.92	54.92
Deal 5	4.60	18	4.60	72.22	72.22
Q3 YTD Fcst	99.27	19	0.00	76.82	0.00
Q3 YTD Bud	98.23	20	0.00	76.82	0.00
Q4 Unsold Churn	6.81	21	6.81	76.82	76.82
Q4 Unsold Medium	4.93	22	4.93	83.63	83.63
Deal 6	5.60	23	5.60	88.56	88.56
Deal 7	1.90	24	1.90	94.16	94.16
FY YTD Fcst	145.44	25	0.00	96.06	0.00
FY YTD Bud	145.44	26	0.00	96.06	0.00

The first 3 columns (Category, CV €M and Index) are the data table that we get from another system, and the last 3 columns are calculated:

CV €M 2 is simply the CV €M column, but Zero when the Category contains "Bud" or "Fcst"

Base 2 is the running total of CV €M 2 but offset by 1 row
Base 3 is Base 2, but Zero when the Category contains "Bud" or "Fcst"

We then use CV €M and Base 3 for the chart data

I am trying to calculate measures in DAX (Not M) to replicate these columns, and I can get to a running total:

Cumulative Base =

CALCULATE (

[CV €M],

FILTER (

ALLSELECTED ( 'FY Chart Data' ),

'FY Chart Data'[Rank] < MAX ( 'FY Chart Data'[Rank] )

)

)

But I am going round in circles tryng to do the offset, and then the Zero values wher the category is "Bud" or "Fcst". Any and all help/pointers would be greatly appreciated.

Thank you

[tamerj1]

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 )

[v-jianboli-msft · Accepted solution]

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.