How to Divide Two Filtered Calculated Sums

[PeterHolland]

Hi there!

I'm trying to create a table which has the headers Sales in 2022, Sales in 2023, then the growth rate between the two, broken out by customer.

My source data looks like this:

Customer	Product Type	Sales 2022	Sales 2023
Anna	Milk	300	400
Jane	Milk	100	100
Mark	Milk	100	200
Anna	Bread	300	400
Jane	Bread	100	0
Mark	Bread	200	100

My desired end result would be this:

Customer	Total Sales, 2022 (sum of sales in 2022)	Total Sales, 2023 (sum of sales in 2023)	Percent Growth (sum of sales in 2023/sum of sales in 2022)-1
Anna	600	800	33%
Jane	200	100	-50%
Mark	300	300	0%

I'm able to get the Total Sales, 2022 and Total Sales, 2023 columns no problem, but am having trouble with the Percent Growth column.

I tried several things, but I think the thing that was closest was this:

Percent Growth =

(CALCULATE(SUM('Table'[Sales 2023]),'Table'[Customer]=EARLIER('Table'[Customer]))

/

CALCULATE(SUM('Table'[Sales 2023]),'Table'[Customer]=EARLIER('Table'[Customer])))

-1

When I use the formula above, I get 0's for Anna and Mark and NaN for Jane

Any ideas how I might do this?

Thank you!

Peter

[PeterHolland]

Thank you so much! This worked. I have marked your solution as accepted!

I think I don't have a good understanding of how measures work 😄

[aj1973 · Accepted solution]

Hi @PeterHolland 

Is this looking good?

If yes simply add 2 measures for Sales 22 and 23 

Sales 22 = SUM(Sheet2[Sales 2022])

Sales 23 = SUM(Sheet2[Sales 2023])

Growth % = DIVIDE([Sales 23] - [Sales 22] , [Sales 22] , 0)

Then Use DIVIDE to have the growth