Home Forums Power Pivot Average by Month – only the months have sales

Tagged: ,

This topic contains 3 replies, has 3 voices, and was last updated by  tomallan 2 years, 5 months ago.

Viewing 4 posts - 1 through 4 (of 4 total)
  • Author
    Posts
  • #3776

    rogeriotortosa
    Participant
    • Started: 6
    • Replies: 12
    • Total: 18

    I have the following data: product, quantity sales month. I would like to know the average sale period only the months have sales

    Product    1    2   3    4   5   6    7    8   9   10   11    12  Total     Average

    Prod_A    0    0  10   0   3  11  10   5  64  20  91  0     214         27  (214 / 8 = 27)

    Prod_B    0    0   0  20  0  10  0   3    3    0    50  0     86        17 (86 / 5 = 17)

    tks

     

    Rogerio

    #3777

    Fahad
    Participant
    • Started: 2
    • Replies: 20
    • Total: 22

    Hi Rogerio,

     

    How is the data structured , can add the file so we can try to help you.

    However, if you want to create a measure that calculate the average for products if they have sales more than 0 then you can use this formula:

    measure:=CALCULATE(AVERAGE(Table[product]),Table[product]>0)

    Thanks

     

    #3778

    rogeriotortosa
    Participant
    • Started: 6
    • Replies: 12
    • Total: 18
    #3783

    tomallan
    Keymaster
    • Started: 9
    • Replies: 2377
    • Total: 2386

    Rogerio,

    I am glad Fahad asked you to look at the data, it changed my point of view entirely.

    If monthly quantities are pre-aggregated (one record per month and per product), your question has already been answered.

    However, if your data could have many records per product per month, then your answer could look like this (excluding any checks to ensure that the measure will be run only on sub-totals and grand totals):

    Average of Monthly Total Quantity Sales :=
    AVERAGEX (
    FILTER (
    ADDCOLUMNS (
    VALUES ( ‘Calendar'[Month] ),
    “Monthly Sales”, CALCULATE ( SUM ( Data[Quantity Sales] ) )
    ),
    [Monthly Sales] > 0
    ),
    [Monthly Sales]
    )

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic.