Forum Discussion

PeterHolland's avatar
PeterHolland
New Member
3 years ago
Solved

How to Divide Two Filtered Calculated Sums

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:

CustomerProduct TypeSales 2022Sales 2023
AnnaMilk300400
JaneMilk100100
MarkMilk100200
AnnaBread300400
JaneBread1000
MarkBread200100

 

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

60080033%
Jane200100-50%
Mark3003000%

 

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

 

Need Help
  • aj1973's avatar
    aj1973
    3 years ago

    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

2 Replies

  • aj1973's avatar
    aj1973
    Community Champion
    3 years ago

    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

  • PeterHolland's avatar
    PeterHolland
    New Member
    3 years ago

    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 😄