Forum Discussion

PBINewbie920's avatar
PBINewbie920
Icon for Helper I rankHelper 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

5 Replies

  • Samarth_18's avatar
    Samarth_18
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi PBINewbie920 ,

     

    Try this:-

    measure = 
AVERAGEX(
	VALUES('date_table'[date]),
	CALCULATE(SUM('Table'[Sales]))
)

     

    Thanks,

    Samarth

    • PBINewbie920's avatar
      PBINewbie920
      Icon for Helper I rankHelper 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
        Icon for Community Champion rankCommunity 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