How to get accumulate sales amount ?

[Paulyeo11]

Hi All

I have below table :-

COMPANY	SALES
AAA	50
BBB	40
CCC	30
DDD	20
EEE	10

Expected result :-

COMPANY	SALES
AAA	50	50
BBB	40	90
CCC	30	120
DDD	20	140
EEE	10	150

My PBI file : https://www.dropbox.com/s/mbv8z894l551row/8020%20V003.pbix?dl=0

https://www.dropbox.com/s/mbv8z894l551row/8020%20V003.pbix?dl=0

[v-zhenbw-msft · Accepted solution]

Hi @Paulyeo11 ,

In addition, we can use two ways to meet your requirement.

1. Create a measure.

Measure = CALCULATE( SUM('TABLE'[SALES]), FILTER( ALLSELECTED('TABLE'),'TABLE'[SALES] >=Max('TABLE'[SALES])))

2. The second way is to add an index column firstly, then create a measure.

CU = CALCULATE(SUM('TABLE'[SALES]),FILTER(ALLSELECTED('TABLE'),'TABLE'[Index]<=MAX('TABLE'[Index])))

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

[v-zhenbw-msft · Accepted solution]

Hi @Paulyeo11 ,

In addition, we can use two ways to meet your requirement.

1. Create a measure.

Measure = CALCULATE( SUM('TABLE'[SALES]), FILTER( ALLSELECTED('TABLE'),'TABLE'[SALES] >=Max('TABLE'[SALES])))

2. The second way is to add an index column firstly, then create a measure.

CU = CALCULATE(SUM('TABLE'[SALES]),FILTER(ALLSELECTED('TABLE'),'TABLE'[Index]<=MAX('TABLE'[Index])))

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

[Paulyeo11]

Hi Sir

Thank you very much for sharing 2 script which is very powerful.

I cannot make it work for my actual PBI file :-

https://www.dropbox.com/s/plg73k6wa7brk01/SI_TDS_V012.pbix?dl=0

Hope you can help me take a look. as i get very funny number

Paul

[v-zhenbw-msft · Accepted solution]

Hi @Paulyeo11 ,

We can add a rank column in TABLE and create a measure to meet your requirement.

1. Create a rank column in TABLE.

rank = 
RANKX('TABLE',CALCULATE(SUM('TABLE'[sales]),FILTER('TABLE','TABLE'[BRAND_C]=EARLIER('TABLE'[BRAND_C]))),,ASC,Dense)

2. Then we can create a measure to get the accumulate sales.

acc1 = 
CALCULATE(SUM('TABLE'[sales]),FILTER(ALLSELECTED('TABLE'),'TABLE'[rank]<=MAX('TABLE'[rank])))

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

[Paulyeo11]

Hi Sir

Thank you very much for your effort , it work now.

Paul

[amitchandak]

@Paulyeo11 ,

Try like

calculate( sum(Table[Sales]), filter( Table,[COMPANY] <=Max(Table[COMPANY])))

or

calculate( sum(Table[Sales]), filter( Table,[Sales] >=Max(Table[Sales])))