Forum Discussion

ptnewman's avatar
ptnewman
Frequent Visitor
9 years ago
Solved

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 th...
Need Help
  • ptnewman's avatar
    ptnewman
    9 years ago

    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])

    )

mattbrice's avatar
mattbrice
Solution Sage
9 years ago

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.

 

ptnewman's avatar
ptnewman
Frequent Visitor
9 years ago

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

  • ptnewman's avatar
    ptnewman
    Frequent Visitor
    9 years ago

    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])

    )