Cumulative Sum Division

[Naveeduddinj077]

Hi Team,

Is it possible to divide a coulum number by sum of other column year wise? I want to know how to get the Yellow highlighted F & G Columns from the below link.

https://docs.google.com/spreadsheets/d/1W8WZSIurQNSft12bKQAsSmtUBAKDggHB/edit#gid=1090238853

For Ex:  Column F / Sum of Column D

Second Ex: Column E / One Year above of Column C

Your help will be highly apprecited.

Regards

Naveed

[v-jingzhang]

Hi @Naveeduddinj077 

You can create two calculated columns with below DAX in your original table

Renewal % = DIVIDE('Table'[Existing],CALCULATE(SUM('Table'[New]),ALL('Table'),'Table'[Calendar Year]<EARLIER('Table'[Calendar Year])))

Renewal % 2 = DIVIDE('Table'[Existing],CALCULATE(SUM('Table'[Total]),ALL('Table'),'Table'[Calendar Year]=EARLIER('Table'[Calendar Year])-1))

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Naveeduddinj]

Hi @v-jingzhang 

I'm getting the below error for second formaula.

[v-jingzhang · Accepted solution]

Hi @Naveeduddinj 

Are you creating a mesure with the DAX? If yes, try replacing EARLIER function with MAX function.

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Naveeduddinj]

Awesome! This works Thank you