cancel
Showing results for
Search instead for
Did you mean:
Frequent Visitor

## Running Total in Matrix with Multiple Rows

I have a matrix in PowerBI with two row filters (name and team) and columns as 12 dates starting from current month. I have a measure that I display for each month, but I want it to display as a running total rather than just as the value for each month.

This is the code I'm currently using:

```Rolling 12 Month = calculate([12 Month],filter(all(Planning[Month]),Planning[Month]<=max(Planning[Month])))

```

where Planning[Month] defines the column header and 12 Month is this measure:

`   12 Month = sum([12MonthK])`

where [12MonthK] is just a column of values.

The only code I've gotten to actually create a running total is this:

`RTTest = If( Not ISEMPTY(Planning),      VAR _maxDate = max(Planning[Month])    Return     calculate(  [YTD 12 Month], Planning[Month]<=_maxDate, all(Planning)))   `

but this running total just completely ignores any filters (the running total is the same for each name/team combo).

I tried using allexcept in the last bit of code, but it just immediately stopped being a running total.

It currently looks like this:

You can see clearly March to April that this is not a running total.

1 ACCEPTED SOLUTION
Community Support

Hi @Brotedo ,

Can you try ALLSELECTED() to do this. Before this, you need create a dim_date table for your fact table. Use the values() to create the dim table which likes the below.

``date = VALUES('Planning'[Month]) ``

Then create a mesure to calculate.

``````c =
CALCULATE (
SUM ( 'Table'[Values] ),
FILTER ( ALLSELECTED ( 'date'[Month] ), [Month] <= MAX ( 'date'[Month] ) )
)
``````

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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

2 REPLIES 2
Community Support

Hi @Brotedo ,

Can you try ALLSELECTED() to do this. Before this, you need create a dim_date table for your fact table. Use the values() to create the dim table which likes the below.

``date = VALUES('Planning'[Month]) ``

Then create a mesure to calculate.

``````c =
CALCULATE (
SUM ( 'Table'[Values] ),
FILTER ( ALLSELECTED ( 'date'[Month] ), [Month] <= MAX ( 'date'[Month] ) )
)
``````

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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

Super User

Hi @Brotedo

If you want to create a running total, you have to have a good model. Currently your model does not look to be good. A good model is a star-schema with fact tables and dimensions. Please consult THIS. If you build a proper Date Table, it'll be rather easy to create a RT. There are myriads of posts on this forum about how to get a RT right (once the model is correct). Please use the search box at the top of the page to find them. Also, you can check YT videos about RT by Alberto Ferrari and Marco Russo. Also, you can look HERE.

Hope this helps 🙂

## Helpful resources

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors