Forum Discussion

PBINewbie920's avatar
PBINewbie920
Helper I
4 years ago
Solved

Average Monthly Sales

Hi,   I needed some help creating a measure/column to calculate the avg monthly sales:   for instance For  Jan 1 = 5/1 = 5 Jan 2 = (5+10)/2 = 7.5 Jan 3 = (5+10+15)/3 = 10 etc.   Any tips?  ...
Need Help
  • Samarth_18's avatar
    Samarth_18
    4 years ago

    PBINewbie920 , Please try this:-

    Measure =
VAR _totalsum =
    CALCULATE (
        SUM ( 'Table (3)'[Sales] ),
        FILTER (
            ALL ( 'Table (3)' ),
            'Table (3)'[Date]
                >= DATE ( YEAR ( MAX ( 'Table (3)'[Date] ) ), MONTH ( MAX ( 'Table (3)'[Date] ) ), 1 )
                && 'Table (3)'[Date] <= MAX ( 'Table (3)'[Date] )
        )
    )
RETURN
    _totalsum / DAY ( MAX ( 'Table (3)'[Date] ) )

    Output:-

     

    Thanks,

    Samarth

PBINewbie920's avatar
PBINewbie920
Helper I
4 years ago

Hi! I tried that, and it looks like it just gives me sales-- not the average. Anything that I am missing?

 

 

Samarth_18's avatar
Samarth_18
Community Champion
4 years ago

PBINewbie920 , Please try this:-

Measure =
VAR _totalsum =
    CALCULATE (
        SUM ( 'Table (3)'[Sales] ),
        FILTER (
            ALL ( 'Table (3)' ),
            'Table (3)'[Date]
                >= DATE ( YEAR ( MAX ( 'Table (3)'[Date] ) ), MONTH ( MAX ( 'Table (3)'[Date] ) ), 1 )
                && 'Table (3)'[Date] <= MAX ( 'Table (3)'[Date] )
        )
    )
RETURN
    _totalsum / DAY ( MAX ( 'Table (3)'[Date] ) )

Output:-

 

Thanks,

Samarth

  • PBINewbie920's avatar
    PBINewbie920
    Helper I
    4 years ago

    Actually i figured it out due to help from another post! For anyone else needing this in the future: 

    VAR _totalsum =
    CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
    ALL ( 'Table' ),
    'Table'[Date]
    >= DATE ( YEAR ( MAX ( 'Table'[Date] ) ), MONTH ( MAX ( 'Table'[Date] ) ), 1 )
    && 'Table'[Date] <= MAX ( 'Table'[Date] ) && 'Table'[Location] = MAX ( 'Table'[Location] )
    )
    )

     

  • PBINewbie920's avatar
    PBINewbie920
    Helper I
    4 years ago

    Thank you! This works when I dont include any other variables, but when I do this happens: 

     

    On the left, with no additional variables it works correctly

    On the right, when i add location, it seems like it adds both locations together to get $105 for Jan 1st, instead of keeping it seperate ($100 for USA Jan 1, $5 for Canda Jan 1)

     

    Is there anything I can do to make it treat these separetly?