Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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!

 

 

 

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
Solution Sage
Solution Sage

Is it possible to show your table?

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.