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.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

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.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors