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

sumx filter issue

i have a table that has sales aggregated by month.  The table has 5 columns:

  • month end date
  • product name
  • beginning sales amount
  • ending sales amount
  • sales delta amount

  i am trying to show the percent increase of sales for any given range of dates (am using date slider).

 

for example my table would have the following:

 

date                product         beginning sales amount   ending sales amount  sales delta amount  

12/31/2016           hat                          9,200                              10,000                               800

12/31/2016          coat                       19,000                              20,000                              1000

1/31/2017             hat                        10,000                              11,000                            1,000

1/31/2017            coat                       20,000                              21,300                            1,300

2/28/2017              hat                       11,000                              12,200                            1,200

2/28/2017            coat                       21,300                              23,500                            2,200

3/31/2017              hat                       12,200                              13,100                               900

3/31/2017            coat                       23,500                              15,000                             1500

user can select 1 or more months, and I want to calculate the % increase in sales by this simple formula

sum(sales delta amount)/sum(beginning sales amount)

 

if my user selects just the month of March 2017, then the % increase is (900+1500)/(12,200+23,500) = 6.7% here 35,700 is the sum of the beginning amount for the earliest month, jan.

if my user selects jan, feb, march 2017, then % increase is (1000+1300+1200+2200+900+1500)/(10000+20000) = 27%  here 30,000 is the sum of the beginning amount for the earliest month, jan.

 

so i always have to find the starting sales amount, divide that into the sum of sales for the whole period.

 

this is what i did:

 

1) create new measure to get the minimum date selected 

first_month_date = min('sales_agg'[month_end_date])

 

2) create new measure to sum all the beginning amounts to create the divisor, what i am trying to do is only sum the records that  have a date equal to the beginning month using the filter and sumx

Starting Amount=sumx(filter('sales_agg','sales_agg'[month_end_date]='sales_agg'[first_month_date ]),'sales_agg'[begin_sales_amt])

 

this sumx(filter()) clause is not working properly, it sums all the records not just those whose date is equal to the first month.  when i display the first month, it is correct, no matter how many months i select, it always is the lowest month of those selected.  just cant figure out why the filter is not working.

 

any help would be greatly appreciated!

 

thanks!

1 ACCEPTED SOLUTION

Eventually tracked down an excel jockey and he provided the answer.  really simple ...

 

Sales Beginning = CALCULATE (

     SUM ( 'sales_agg'[beginning sales amount] ),

     FIRSTDATE('sales_agg'[begin_sales_amt])

)

View solution in original post

4 REPLIES 4
ptnewman
Frequent Visitor

whoops small typo

 

if my user selects just the month of March 2017, then the % increase is (900+1500)/(12,200+23,500) = 6.7% here 35,700 is the sum of the beginning amount for the earliest month, jan.

 

should read

 

if my user selects just the month of March 2017, then the % increase is (900+1500)/(12,200+23,500) = 6.7% here 35,700 is the sum of the beginning amount for the earliest month, March.

 

 

How about:

 

Sales Delta Amt =
CALCULATE (
    SUM ( 'sales_agg'[sales delta amount] ),
    ALLSELECTED ( Calendar[Date] )
)

Sales Beginning =
CALCULATE (
    SUM ( 'sales_agg'[beginning sales amount] ),
    ALLSELECTED ( Calendar[Date] )
)


% increase = DIVIDE ( [Sales Delta Amt] , [Sales Beginning] )

 which assumes you are using a separate best practice calendar table...if not you may be able to switch out 'Calendar[Date]' with 'sales_agg'[month end date]  inside the ALLSELECTED() call.

 

Matt, thanks for chiming in.  

 

I need the sales beginning to only sum the earliest's months "beginning sales amount".  this sum would be the total sales at the beginning of the period.  if my user selects 3 months, i believe your calculation would sum the begininng amounts for all 3 months, which will not work.  I will look at the calculate / sum / allselected to see if there is something i can use there.  

 

thanks, Paul

Eventually tracked down an excel jockey and he provided the answer.  really simple ...

 

Sales Beginning = CALCULATE (

     SUM ( 'sales_agg'[beginning sales amount] ),

     FIRSTDATE('sales_agg'[begin_sales_amt])

)

View solution in original post

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
Top Kudoed Authors