Cumulative Sum across years by category

[sanjeev803]

Hi -  I have data for four years. I need a cumulative sum and cumulative percentage across years by sub-category.

sample data:

Sub-Category	Total Qty
Binders	5818
Paper	5035
Furnishings	3484
Phones	3220
Storage	3075
Accessories	2911
Art	2883
Chairs	2295
Appliances	1706
Labels	1358
Tables	1212
Envelopes	891
Fasteners	888
Bookcases	837
Supplies	643
Machines	424
Copiers	234

I need output like this:

Sub-Category	Sum of Quantity	Csum	Csum%
Binders	5818	5818	16%
Paper	5035	10853	29%
Furnishings	3484	14337	39%
Phones	3220	17557	48%
Storage	3075	20632	56%
Accessories	2911	23543	64%
Art	2883	26426	72%
Chairs	2295	28721	78%
Appliances	1706	30427	82%
Labels	1358	31785	86%
Tables	1212	32997	89%
Envelopes	891	33888	92%
Fasteners	888	34776	94%
Bookcases	837	35613	96%
Supplies	643	36256	98%
Machines	424	36680	99%
Copiers	234	36914	100%

Any help of this highly appreciated.

Thanks,

Sanj

[HotChilli]

Add an Index in Power Query Editor.

Create a measure as follows

CSum = CALCULATE(SUM(Table[Total Qty]), FILTER(ALL(Table), Table1[Index] <= MAX(Table[Index])))

Drag the measure on to the table visualisation twice. 

The second one --> right-click in the Values well and choose 'Show value as -> Percent of grand total'

[parry2k · Accepted solution]

@sanjeev803 try following measures:

1st measure - create sum of qty

2nd measure - cummulative sum 

3rd measure - create grand total of qty for %

4th measure - create %

Sum = SUM( Table2[Total Qty] )

Sum Cummulative = 
IF( 
    HASONEVALUE( Table2[Sub-Category] ),  
    SUMX( FILTER( ALL( Table2 ), Table2[Total Qty] >= MAX( [Total Qty] ) ), [Sum] ),
    [Sum]
)

Sum Total = CALCULATE( [Sum], ALL( Table2[Sub-Category] )  )

Sum % = DIVIDE( [Sum Cummulative], [Sum Total] )