Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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?
Solved! Go to Solution.
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
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.
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.
Below are my two tables for dax
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
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 Day | Running Total | Desired Result |
0 | 2 | 2 | 2 |
1 | 6 | 8 | 8 |
2 | 4 | 12 | 12 |
3 | 6 | 18 | 18 |
4 | 6 | 24 | 24 |
5 | 5 | 29 | 29 |
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]) )))
Hmm, well, the slicers aren't working because of your ALL clause, you might try ALLEXCEPT and list the columns of your slicers.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
82 | |
71 | |
49 |
User | Count |
---|---|
143 | |
121 | |
111 | |
59 | |
57 |