topic Re: Cumulative SUM in DAX Commands and Tips

Cumulative SUM

Nicharee2205 — Mon, 08 Jul 2024 09:11:06 GMT

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.

Re: Cumulative SUM

foodd — Mon, 08 Jul 2024 09:12:35 GMT

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.

Re: Cumulative SUM

BeaBF — Mon, 08 Jul 2024 12:43:48 GMT

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
)

Re: Cumulative SUM

Nicharee2205 — Tue, 09 Jul 2024 00:42:38 GMT

Thankyou, actually I also try this:

and it works too