cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Brotedo
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:

Brotedo_0-1659034002629.png

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

 

 

1 ACCEPTED SOLUTION
v-chenwuz-msft
Community Support
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:

vchenwuzmsft_0-1659410808080.png

 

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.

View solution in original post

2 REPLIES 2
v-chenwuz-msft
Community Support
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:

vchenwuzmsft_0-1659410808080.png

 

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.

daXtreme
Super User
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
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

BizApps LATAM 2023

Business Application LATAM Summit 2023

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

Power Platform Bootcamp

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