Forum Discussion

Rodrivieiras's avatar
Rodrivieiras
Helper I
4 years ago
Solved

Average formula

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's avatar
    tamerj1
    4 years ago

    Rodrivieiras 
    Again my mistake

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

5 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    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's avatar
      Rodrivieiras
      Helper I
      4 years ago

      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's avatar
        tamerj1
        Community Champion
        4 years ago

        Rodrivieiras 
        Again my mistake

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