Power Bi - Create Measure "Running Total" based on column values, line and stacked column bar chart

[Anonymous]

Hi all,

I am trying to build a running total measure but have been struggling with the following constraints I have.

My database is as follow:

5 columns: Indicator, Scenario, Asset Name, Date, Value

Indicator = name, e.g. Costs, Revenue,

Scenario = 60$, 90$

Asset Name = Plant 1, Plant 2, Plant 3

Date = in DD/MM/YYYY format

Value = values = results of each indicator

In the chart below, when I select a specific scenario, indicator, date range and asset name, I get the following result in red (bars). This represents e.g. the FCF before corporate items in yearly figures for the asset "plant 1" et the economic scenarios "60$" and "90$".

Yellow running total is obviously wrong. I would like to get two running total lines each one representing the running total of bars from scenario "60$" and the other for scenario "90$". In other terms, it means that my running total is showing the running total of each of the scenarios separately. Also, if I change the indicator "FCF before corporate elements" by "Indicator XX2"  my running total would adapt in the chart.

Is there a way to calculate a running total depending on many columns values? In other terms, I define the specific indicator, scenario, asset name, period range I want to see in the chart and the runnning total appears on it

Thanks a LOT for your help I am lost 

[Anonymous]

Hi and thanks a lot for your help.

I think you are close to it but there is still something missing. I get a correct running total but there is only one line connecting the 60$ case and the 90$ case. In fact, I would like to have two lines. One cumulative for the 60$ case and one for the 90$ case. Please see below as an example

thanks a lot 🙂

[v-yanjiang-msft]

Hi @Anonymous ,

According to your description, do you mean want to get the running total value by date at the current Indicator, Scenario, Asset Name? If so, please try the below formula:

Measure =
SUMX (
    FILTER (
        ALL ( 'Table' ),
        [Indicator] = SELECTEDVALUE ( [Indicator] )
            && [Scenario] = SELECTEDVALUE ( [Scenario] )
            && [Asset Name] = SELECTEDVALUE ( [Asset Name] )
            && [Date] <= MAX ( [Date] )
    ),
    [Value]
)

If it doesn't work, please provide a sample and expected result.

Best Regards,
Community Support Team _ kalyj

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