cancel
Showing results 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.

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':

Data Looks like this as below;

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
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
Helper III

@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.

 Team Defects Cumulative Defects A 32 32 C 20 52 E 16 68 J 16 84 U 10 94 K 8 102 O 0 102
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper III

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.

Super User

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper III

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

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.

 Team Defects Cumulative Defects A 32 32 C 20 52 E 16 68 J 16 84 U 10 94 K 8 102 O 0 102
Community Support

@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])))``

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

Helper III

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!!

Super User

Try a new measure like

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

Helper III

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

Super User

@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])))

Helper III

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.

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)

Helper III

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

Announcements

#### 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

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors