Average by Month – only the months have sales

[rogeriotortosaParticipant Started: 6Replies: 12Total: 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

[FahadParticipant Started: 0Replies: 6Total: 6]

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

 

[rogeriotortosaParticipant Started: 6Replies: 12Total: 18]

following data

https://onedrive.live.com/redir?resid=D63D4E231E74BED!2111&authkey=!ABTQT1h-9sJ_zio&ithint=file%2cxlsb

tks

[tomallanKeymaster Started: 0Replies: 417Total: 417]

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

[Author]

Posts