Forum Discussion

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...

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

Frequent Visitor

9 years ago

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.

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

Related content