Forum Discussion
Cumulative SUM
Hi everyone,
I'm working on a Power BI report where I need to calculate a cumulative count of unfinished work and plot it by month. Additionally, I need this measure to respect multiple slicers including Tag Type, Urgent Level, Ranking, Department, Location, Internal Serial, Failure Type, Repair Type, and Response By User.Here's what I have so far:
CumulativeTest2 =
VAR CurrentDate = MAX('BD Case'[ConvertedDate])
VAR FilteredTable =
FILTER(
ALL('BD Case'),
'BD Case'[ConvertedDate] <= CurrentDate &&
(ISBLANK(SELECTEDVALUE('BD Case'[Tag Type])) || 'BD Case'[Tag Type] = SELECTEDVALUE('BD Case'[Tag Type])) &&
(ISBLANK(SELECTEDVALUE('BD Case'[Urgent Level])) || 'BD Case'[Urgent Level] = SELECTEDVALUE('BD Case'[Urgent Level])) &&
(ISBLANK(SELECTEDVALUE('BD Case'[RankingforSlicer])) || 'BD Case'[RankingforSlicer] = SELECTEDVALUE('BD Case'[RankingforSlicer])) &&
(ISBLANK(SELECTEDVALUE('BD Case'[departmentName])) || 'BD Case'[departmentName] = SELECTEDVALUE('BD Case'[departmentName])) &&
(ISBLANK(SELECTEDVALUE('BD Case'[LocationParentName])) || 'BD Case'[LocationParentName] = SELECTEDVALUE('BD Case'[LocationParentName])) &&
(ISBLANK(SELECTEDVALUE('BD Case'[internalSerial])) || 'BD Case'[internalSerial] = SELECTEDVALUE('BD Case'[internalSerial])) &&
(ISBLANK(SELECTEDVALUE('BD Case'[Failure Type])) || 'BD Case'[Failure Type] = SELECTEDVALUE('BD Case'[Failure Type])) &&
(ISBLANK(SELECTEDVALUE('BD Case'[repairType])) || 'BD Case'[repairType] = SELECTEDVALUE('BD Case'[repairType])) &&
(ISBLANK(SELECTEDVALUE('BD Case'[responseByUser])) || 'BD Case'[responseByUser] = SELECTEDVALUE('BD Case'[responseByUser])))
RETURN
SUMX(FilteredTable, 'BD Case'[IsUnfinished])
IsUnfinished column show ther right results like this
Now card count unfinished is right but cumulative sum is not.
Nicharee2205 Hi!
Try with:
CumulativeTest2 =
VAR CurrentDate = MAX('BD Case'[ConvertedDate])
RETURN
CALCULATE(
COUNTROWS('BD Case'),
FILTER(
ALLSELECTED('BD Case'),
'BD Case'[ConvertedDate] <= CurrentDate &&
(ISBLANK(SELECTEDVALUE('BD Case'[Tag Type])) || 'BD Case'[Tag Type] = SELECTEDVALUE('BD Case'[Tag Type])) &&
(ISBLANK(SELECTEDVALUE('BD Case'[Urgent Level])) || 'BD Case'[Urgent Level] = SELECTEDVALUE('BD Case'[Urgent Level])) &&
(ISBLANK(SELECTEDVALUE('BD Case'[RankingforSlicer])) || 'BD Case'[RankingforSlicer] = SELECTEDVALUE('BD Case'[RankingforSlicer])) &&
(ISBLANK(SELECTEDVALUE('BD Case'[departmentName])) || 'BD Case'[departmentName] = SELECTEDVALUE('BD Case'[departmentName])) &&
(ISBLANK(SELECTEDVALUE('BD Case'[LocationParentName])) || 'BD Case'[LocationParentName] = SELECTEDVALUE('BD Case'[LocationParentName])) &&
(ISBLANK(SELECTEDVALUE('BD Case'[internalSerial])) || 'BD Case'[internalSerial] = SELECTEDVALUE('BD Case'[internalSerial])) &&
(ISBLANK(SELECTEDVALUE('BD Case'[Failure Type])) || 'BD Case'[Failure Type] = SELECTEDVALUE('BD Case'[Failure Type])) &&
(ISBLANK(SELECTEDVALUE('BD Case'[repairType])) || 'BD Case'[repairType] = SELECTEDVALUE('BD Case'[repairType])) &&
(ISBLANK(SELECTEDVALUE('BD Case'[responseByUser])) || 'BD Case'[responseByUser] = SELECTEDVALUE('BD Case'[responseByUser]))
),
REMOVEFILTERS('BD Case'[ConvertedDate]) // Ensures cumulative count across all dates up to CurrentDate
)
3 Replies
Nicharee2205 Hi!
Try with:
CumulativeTest2 =
VAR CurrentDate = MAX('BD Case'[ConvertedDate])
RETURN
CALCULATE(
COUNTROWS('BD Case'),
FILTER(
ALLSELECTED('BD Case'),
'BD Case'[ConvertedDate] <= CurrentDate &&
(ISBLANK(SELECTEDVALUE('BD Case'[Tag Type])) || 'BD Case'[Tag Type] = SELECTEDVALUE('BD Case'[Tag Type])) &&
(ISBLANK(SELECTEDVALUE('BD Case'[Urgent Level])) || 'BD Case'[Urgent Level] = SELECTEDVALUE('BD Case'[Urgent Level])) &&
(ISBLANK(SELECTEDVALUE('BD Case'[RankingforSlicer])) || 'BD Case'[RankingforSlicer] = SELECTEDVALUE('BD Case'[RankingforSlicer])) &&
(ISBLANK(SELECTEDVALUE('BD Case'[departmentName])) || 'BD Case'[departmentName] = SELECTEDVALUE('BD Case'[departmentName])) &&
(ISBLANK(SELECTEDVALUE('BD Case'[LocationParentName])) || 'BD Case'[LocationParentName] = SELECTEDVALUE('BD Case'[LocationParentName])) &&
(ISBLANK(SELECTEDVALUE('BD Case'[internalSerial])) || 'BD Case'[internalSerial] = SELECTEDVALUE('BD Case'[internalSerial])) &&
(ISBLANK(SELECTEDVALUE('BD Case'[Failure Type])) || 'BD Case'[Failure Type] = SELECTEDVALUE('BD Case'[Failure Type])) &&
(ISBLANK(SELECTEDVALUE('BD Case'[repairType])) || 'BD Case'[repairType] = SELECTEDVALUE('BD Case'[repairType])) &&
(ISBLANK(SELECTEDVALUE('BD Case'[responseByUser])) || 'BD Case'[responseByUser] = SELECTEDVALUE('BD Case'[responseByUser]))
),
REMOVEFILTERS('BD Case'[ConvertedDate]) // Ensures cumulative count across all dates up to CurrentDate
)Thankyou, actually I also try this:
and it works too
Hello Nicharee2205 , and thank you for sharing a question with the Community. The following is informational. Please remember to adhere to the decorum of the Community Forum when asking a question.
Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot). You can upload the PBIX file to a cloud storage service such as OneDrive, Google Drive, Dropbox, or to a Github repository, and then share a file’s URL.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523/highlight/true#M607150
This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.
