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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
navedkhan
Helper III
Helper III

URGENT HELP SOUGHT - Calculate "Cumulative Total" column by way of DAX without repeating values

HI Friends - i need your help in building DAX logic for a calculated column for 'Cumulative Total':

 

@Zubair_Muhammad 
@Ashish_Mathur 
@Greg_Deckler 

 

Data Looks like this as below;

Capture.PNG

I've tried using various syntax but the problem arises when values are same/repeated (for Team D& E) in 'Defects' column. My DAX ideally should aggregate one by one all the values from 'Defects' Column and show it in 'Cumulative Defects' column.

 

So i need
1) Measure by way of DAX to aggregate values one after another irrespective if they repeated values or are Null.
2) Defects column is desc so measure to calculate accordingly

 

 

Thanks,

NK

 

14 REPLIES 14
Ashish_Mathur
Super User
Super User

Hi,

Share the link from where i can download your PBI file or paste data here in a format that i can take it to MS Excel.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur sure i've attached excel sheet with the data

 

Cumulative Defects Column i wanted in PowerBi wherein DAX to incrementally add values irrespective of duplicate values or NULL values in the Defects Column.

 

TeamDefectsCumulative Defects
A3232
C2052
E1668
J1684
U1094
K8102
O0102

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Now the problem am getting is this;

 

when a slicer of date is applied my 'Cumulative Totals' column doesn't show right values! And when Slicer is 'All Selected', i get cumulative totals right.

 

IMG-5739.JPG

I do not know whom you are replying to but if it's me, then i need to see your PBI file.  Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Anyone please can help resolve this? so far none of the suggested approaches have worked in my problem.

@Ashish_Mathur @amitchandak ?

Like we simply do it in excel, how do i build a running total column in powerbi to incrementally add values even if they are repeating or are NULL.

 

TeamDefectsCumulative Defects
A3232
C2052
E1668
J1684
U1094
K8102
O0102

@navedkhan 

When you have repeated or null values, you could first add a Index column in Query editor, and use that index to calculate the cumulative total.

 

Measure = CALCULATE(SUM('Table'[Defects]),FILTER(ALL('Table'),'Table'[Index]<=MAX('Table'[Index])))

 

 

index running total.JPG

 

Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks a ton @V-pazhen-msft it does work but if i've to apply "Date" to this table, then Indexing doesn't work as it's a static column.

may be "dynamic indexing" is the way forward from here? 

 

Pls advise so as to close this as a solution!!

amitchandak
Super User
Super User

@navedkhan ,

Try a new measure like

calculate(sum(Table[defects]),filter(all(Table),Table[Team name]<=max(Table[Team name])))

Thanks @Amit but if my 'Defects' column is sorted 'Desc' then how would this measure aggregate?

Capture.PNG

@navedkhan , try these. Not tested

calculate(sum(Table[defects]),filter(all(Table),Table[defects]<=max(Table[defects])))

calculate(sum(Table[defects]),filter(all(Table),Table[defects]>=min(Table[defects])))

Thanks @amitchandak we are close but still far.
your 2nd measure works but then for duplicates value it doesn't handle them as i wanted. see result of 2nd measure;

see teams C & D show same values which is not what i want. it should rather show 68 / 93 / 106 / 124 / 133 / 135 ... etc.

IMG-5736.JPG

danno
Resolver V
Resolver V

Have you tried Add Quick Measure in Power BI? There is a Cumulative Total Pattern in there (you will need a Calendar Table as well) 

I needed to have a column which can cumulatively aggregate even if values repeat or are NULL in a given value column

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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