cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ptnewman
Frequent Visitor

How to calculate % increase

Hi, I am somewhat new to powerbi and have been struggling with a sales report.  The sales report shows a monthly increase in sales, not the sales total.  What I have so far is a table aggregated by month with a sales region, total sales amount, and sales delta from the prior month (this month sales - last month sales).  Then I display the delta using a waterfall visual and it looks great.  Have 2 slicers on date and sales region.  requirement now is to show the % increase from the beginning period selected.  So if I select 2016, I currently show a waterfall with 12 months and it shows the total increase for the year.  I want to calculate the % increase but can't figure out how.  I added the prior year ending sales amount to my table.  But that value then repeats for every month so I can't aggregate the value.  To further complicate the matter, I may want to select just a single quarter and see the % increase just for that quarter.  Thinking that I should have the starting sales amount for each month, but then only select the first month's value when calculating the % increase.  For example if I selected 2016, I would grab the starting sales value from January and use it as the denominator.  Just can't figure out how to just get that value when the report is showing all 12 months.

 

Any help would be greatly appreciated.

 

thanks!

1 ACCEPTED SOLUTION
ptnewman
Frequent Visitor

Took me much digging and searching but i found a solution.  And now looking at the solution, it seems quite easy.  For me not so much because i am not an excel warrior and have no prior dax programming experience.

 

To summarize, i have a sales table that is aggregated monthly.  My requirement is to show monthly increase/decrease in sales which can be filtered by region.  additionally, i want to show the percent increase from the prior year.  i added to my sales agg, the delta between this month's sales and the prior month.  i also added the prior years sales amount.  all this is stored at the month/sales region grain.  i allow the report to restrict dates to year, quarter, or month.  Percent increase is the sum of the deltas divided by the prior year sales amount.  if i select a year, its 12 months of deltas divided by prior year.  if i select a quarter its 3 months of deltas divided by prior year.  problem is the prior year amount repeats across all monthly records, so when i select 3 months powerbi wants to sum the deltas of each month but also wants to sum the prior year amount of all 3 months.   obviously that will not work.  

 

my solution is to create custom measures:

 

first calculate the prior year amount that will not change regardless of how many months are selected:

 

Sales Prior Year Amount = sum([sales_last_year_amt])/distinctcount([month_end_date])

 

next calculate the percent increase, using prior year sales amount calculated above:

 

Sales Percent Increase = divide(sum([sales_delta_amt]),[Sales Prior Year Amount])

 

Easy solution now that i look at it, but it took me 2 days to figure it out 🙂

 

My report is using 2 waterfall visuals, one for the sales amount increase/decrease, one for the sales % increase/decrease.  when i select a year, it looks great, shows starting point to ending and all the 12 changes between.  i also have a summary card that shows the sales amount beginning, ending, delta, and % increase.  I can slice by sales region and/or by time.  though the report will not work properly if we span years (as the prior year sales amount will change across years and foul the results).  my business users understand this and use the report as intended.  I may add some code that prevents the report from calculating percentages when multiple years are selected.  

 

thanks!

 

 

 

View solution in original post

5 REPLIES 5
ptnewman
Frequent Visitor

Took me much digging and searching but i found a solution.  And now looking at the solution, it seems quite easy.  For me not so much because i am not an excel warrior and have no prior dax programming experience.

 

To summarize, i have a sales table that is aggregated monthly.  My requirement is to show monthly increase/decrease in sales which can be filtered by region.  additionally, i want to show the percent increase from the prior year.  i added to my sales agg, the delta between this month's sales and the prior month.  i also added the prior years sales amount.  all this is stored at the month/sales region grain.  i allow the report to restrict dates to year, quarter, or month.  Percent increase is the sum of the deltas divided by the prior year sales amount.  if i select a year, its 12 months of deltas divided by prior year.  if i select a quarter its 3 months of deltas divided by prior year.  problem is the prior year amount repeats across all monthly records, so when i select 3 months powerbi wants to sum the deltas of each month but also wants to sum the prior year amount of all 3 months.   obviously that will not work.  

 

my solution is to create custom measures:

 

first calculate the prior year amount that will not change regardless of how many months are selected:

 

Sales Prior Year Amount = sum([sales_last_year_amt])/distinctcount([month_end_date])

 

next calculate the percent increase, using prior year sales amount calculated above:

 

Sales Percent Increase = divide(sum([sales_delta_amt]),[Sales Prior Year Amount])

 

Easy solution now that i look at it, but it took me 2 days to figure it out 🙂

 

My report is using 2 waterfall visuals, one for the sales amount increase/decrease, one for the sales % increase/decrease.  when i select a year, it looks great, shows starting point to ending and all the 12 changes between.  i also have a summary card that shows the sales amount beginning, ending, delta, and % increase.  I can slice by sales region and/or by time.  though the report will not work properly if we span years (as the prior year sales amount will change across years and foul the results).  my business users understand this and use the report as intended.  I may add some code that prevents the report from calculating percentages when multiple years are selected.  

 

thanks!

 

 

 

View solution in original post

Hi @ptnewman,

I am very gald you have resloved your issue, please mark your solution as answer. So that other people having similar problems will find workaround easily. Thanks for understanding.

Best Regards,
Angelia

rocky09
Impactful Individual
Impactful Individual

Is it possible to show your table?

ptnewman
Frequent Visitor

this is a simplified version as there are a few hierarchies and different metrics as well.  but this is the essence of it.

 

the data is all aggregated here to the month.  and i have precalculated the delta from this month to last.  so my waterfall report really just uses the month end date and sales delta amount.  and then i have a timeline slicer to pick a period and can optionally filter to a sales region.  my percent increase is simply the sum(delta amount) / prior period sales amount.  the prior period sales amount is the "sales last month amount" for the first month that is selected.  if i select 2016 then the prior period sales is from the january 2016 record.  if i pick Q2 2016, then the prior period sales is from the april 2016 record.

 

thanks!

 

CREATE TABLE [dbo].[sales_growth_agg]
(
[month_end_date] [date] NOT NULL,
[last_year_end_date] [date] NOT NULL,
[last_quarter_end_date] [date] NOT NULL,
[sales_region] [nvarchar](255) NOT NULL,
[sales_this_month_amt] [money] NOT NULL,
[sales_delta_amt] [money] NOT NULL,
[sales_last_month_amt] [money] NOT NULL,
[sales_last_year_amt] [money] NOT NULL,
[sales_last_quarter_amt] [money] NOT NULL
)

GO

 

Hi @ptnewman,

In your table, [last_quarter_end_date] is the last date in a quarter? What's the meaning of the sum(delta amount)? Based on my understanding, you'd better add a flag for last month in each, then calculated the sum sales in last month of quarter. 

Could you please share your .pbix file or sample data rather than an empty table? So than we can given solution is close to your requirement. 

Best Regards,
Angelia


Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors