Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
user10
Frequent Visitor

Applying filters to running total?

Hi,

 

I'm trying to create a chart that shows the progressive work completed on a project; So at week 1 0% (0hours) have been completed; by week 50 (end of project), it should show 100% (1000hours) complete.  I've added a column to my table to calculate the running total on hours completed per week.

 

CumulativeHours = CALCULATE ( SUM (WORK[TotalHours]), ALL(WORK), WORK[Week] <= Earlier(WORK[week]))

 

My tables look like this

 

ACTIVITIES (list of all activities)

Week

ActivityName

TypeOfWork

WorkArea

ActivityHours

 

WORK (distinct list of weeks and total hours per week, from the ACTIVITIES table)

Week

TotalHours

CumulativeHours

 

Tables are joined on the 'Week' Column (relationship is 1 (WORK) : M (ACTIVITIES)).

 

I want to add slicers to the report for TypeOfWork and WorkArea.  I have 2 issues:  1. The slicers do not seem to affect the chart when I apply them.  2. Even if the slicers worked, I'm not conviced the cumulativeHours would re-calculate based on the applied filters...?

 

Has anyone come across the same problem and have a solution?

1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

Hi, @user10

 

You want to make the cumulativeHours re-calculate based on the selected filters, right? If so, please create a measure to calculate running total rather than calculated column.

 

The DAX formula should be like:

 

MeasureRunningTotal = 
CALCULATE(
SUM(Running[TotalDay]),
FILTER(ALLSELECTED(Running),
Running[WeekName]<=MAX(Running[WeekName])
))

Thanks,
Yuliana Gu

 

Community Support Team _ Yuliana Gu
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

10 REPLIES 10
user10
Frequent Visitor

Both solutions worked.  Thanks to @Greg_Deckler and @v-yulgu-msft!

 

 

Anonymous
Not applicable

Hi Greg,

 

I noticed u have provided some examples for calculating cumalative measures . So my scenario is i need to calculate a cumm total of all funds where work has commenced using a weekending column and team leaders should be able to filter using a slicer..the formula works before adding the slicer but falls over the moment i try to slice. The slicer im applying is in the eoy_AllFunds table.

 

Started_Cum_Actual = CALCULATE(count(eoy_AllFunds[Started Week Ending -2019]),filter(ALL('Plan Started'),('Plan Started'[Week Ending]<=max('Plan Started'[Week Ending]))))

Sample data would be great. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

The issue is probably your ALL clause. You should try ALLSELECTED or perhaps ALLEXCEPT instead of ALL in order to maintain the filter context provided by your slicer.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

eoy.JPGplan.JPG

Anonymous
Not applicable

Below are my two tables for dax 

Started_Cum_Actual = CALCULATE(count(eoy_AllFunds[Started Week Ending -2019]),filter(all('Plan Started'),'Plan Started'[Week Ending]<=max('Plan Started'[Week Ending])))
 
They have a many(oy_AllFunds) to one (Plan Started) relationship between them and the slicer needs to go on em csm
 
v-yulgu-msft
Employee
Employee

Hi, @user10

 

You want to make the cumulativeHours re-calculate based on the selected filters, right? If so, please create a measure to calculate running total rather than calculated column.

 

The DAX formula should be like:

 

MeasureRunningTotal = 
CALCULATE(
SUM(Running[TotalDay]),
FILTER(ALLSELECTED(Running),
Running[WeekName]<=MAX(Running[WeekName])
))

Thanks,
Yuliana Gu

 

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hey why doesn't this work for a calculated column? Is it that a calculated column doesn't re-evaluate inside a visual, so ignores any filter context? 

I tried the formula but it keep repeating the first number after that. It has 2 slicers, shop and date. Users select a shop and a date range. The running total is on the number of days machines are running during the selected date range for the selected shop.

 

Shop A has only 5 machines. But the running total ran past the maximum of 5 for Shop A. I think that's because other shops have more machines. One of the shop have 12 machines.

 

# of Machine# of DayRunning TotalDesired Result
0222
1688
241212
361818
462424
552929
6 2 
7 2 
8 2 
9 2 
10 2 
11 2 
12 2 
    

 

 

RunningTotal =  Calculate (SUM(Machine[MachineCount]), FILTER(ALL(Machine[NumberOfMachine], Machine[Shop]),

 

(Machine[NumberOfMachine] <= max(Machine[NumberOfMachine])  )))

 

Greg_Deckler
Super User
Super User

Hmm, well, the slicers aren't working because of your ALL clause, you might try ALLEXCEPT and list the columns of your slicers.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thank you @Greg_Deckler 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.