Average formula

[Rodrivieiras]

Hello, I'm starting Power BI studies and I have a table called ProductSales with millions of data to analyze. Below is a sample of the data

PERIOD	PRODUCT	PRICE
2001-07	TOY	10
2001-07	DOLL	30
2001-07	TOY	15
2001-08	DOLL	10
2001-08	BALL	20
2001-08	TOY	30
2001-08	TOY	15
2001-08	DOLL	10
2001-08	BALL	30
2001-08	TOY	50

I want to calculate the average sales of each product per month. In other words, as there were 2 sales of TOYS in July and 3 sales of TOYS in August, the average sales of TOYS is 2.5 per month. For DOLLS the result is 2 and for BALLS the result is 1.

I tried using the following DAX formula, but it didn't work:

AverageSold = AVERAGEX(VALUES(Calendar[Month]), 

                        COUNTROWS('ProductSales')

)

How can i solve t?

Thank you!

[tamerj1]

@Rodrivieiras 

Sorry I misunderstood the question. Your approach is correct except a small detail.  If you are slicing by product then 

AverageSold = DIVIDE ( COUNTROWS('ProductSales'), VALUES(Calendar[Month])

)

[Rodrivieiras]

Hi @tamerj1,

I tried your solution, but although the measure compiles successfully, I don't know how to check the result.

I used a card, but the following error message appeared:

"A table of multiple values was provided where a single value was expected"

The table view also didn't work.

Any clue?

Thanks

[tamerj1 · Accepted solution]

@Rodrivieiras 
Again my mistake

AverageSold =
DIVIDE (
    COUNTROWS ( 'ProductSales' ),
    COUNTROWS ( VALUES ( Calendar[Month] ) )
)

[Rodrivieiras]

Hi @tamerj1 ,

I just changed your formula a little bit by:

AverageSold =

DIVIDE (

              COUNTROWS( ProductSales ),

             COUNTROWS ( VALUES(ProductSales[PERIOD] ) 

))

It worked perfectly!

Thanks a lot!

[tamerj1]

Hi @Rodrivieiras 

have you tried sumple AVERAGE?