Forum Discussion

Vvelarde's avatar
Vvelarde
Community Champion
9 years ago

Cumulative sum by column no date

Hi, i have a little problem unsolved with a dax cumulative.   i have this table   Category Value A 1 A 1 B 1 C 1 C 1 D 1   I need to create a cumulative sum by ca...
Need Help
Vvelarde's avatar
Vvelarde
Community Champion
9 years ago

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

Vvelarde's avatar
Vvelarde
Community Champion
9 years ago

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.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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

        

    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