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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Vvelarde
Community Champion
Community Champion

Cumulative sum by column no date

Hi, i have a little problem unsolved with a dax cumulative.

 

i have this table

 

CategoryValue
A1
A1
B1
C1
C1
D1

 

I need to create a cumulative sum by category

 

So the result is this:

 

Category SumValues Cumulative

A                2                   2

C               2                    4

B               1                    5

D              1                    6

Total       6 

 

How can i calculate the cumulative?

 

Thanks for help

 




Lima - Peru
4 REPLIES 4
v-qiuyu-msft
Community Support
Community Support

Hi @Vvelarde,

 

In your scenario, why do you want to get the result based on the sorting A-> C-> B-> D. If we Group By based on Category in Query Editor, we can't sort by SumValues firstly then by Category. If we based on the normal category sorting, we can add a index column to calculate the running total easily.

 

Best Regards,

Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

hi @v-qiuyu-msft

 

I need to show order the category (A,B,C,D) by Sum of values. To Show Correctly a Cumulative Sum..Starting from Highest to Lowest.

 

 My next step is show a measure  in %.

 

A   2     (2/6)

C   2     (4/6)

B   1      (5/6)

D   1      (6/6)

Total 6

 

This is the reason of i don't show you in normal sorting




Lima - Peru

I had these measure that works when the sum of values is different by each category.

 

ValuesAcumulado = 
VAR valor=Calculate(Sum(Table1[Values])
RETURN
SUMX(FILTER(All('Category');[SumaValues]>=valor);[SumaValues])

Where Category is a single table column with A,B,C,D and SumaValues = Calculate(Sum(Table1[Values]))

 

The problem is when have  same sum values for 2 or more categories. The Sum takes all the categories with same values.

 




Lima - Peru

Hi @Vvelarde

 

Here  is how I did.

 

1. Created a Summarized table Table5 with columns Category as  Cat   and sum Values  - Result from source table.

2. Created a running sequence number column using

     IndexCat = CALCULATE(COUNTA(Table5[Cat]), FIlter(Table5,Table5[Cat]<=EARLIER(Table5[Cat])))

3. Created a Rank column 

    ModRank = RANKX(ALL(Table5), [Result]+[IndexCat]/100000,,0,Dense)

4. By adding IndexCat/100000 there will be no two values with same Rank even though the value of Result is the same.

5. Created a **bleep** total column

    CumTot = CALCULATE(sum(Table5[Result]), FIlter(Table5,Table5[ModRank]<=EARLIER(Table5[ModRank])))

6. the output I got in a table format is

    Capture.GIF

The only issue I could not resolve yet is to get the Cat in the ascending order of cat within same values of Reult.

May be you could throw some light on it.

 

Cheers

 

CheenuSing

 

 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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