AVERAGEX not fixed result

[davide_rossi]

Good morning,

I am trying to compute a monthly average consumption per customer per selling point, 

what I would like to have is, for a given selling point, for a given month, how much is the average consumption per customer?

I used:

AvgSales = CALCULATE (

                          AVERAGEX (

                                  SUMMARIZE (

                                             'IT trans cc vegacrm2',

                                             'IT trans cc vegacrm2'[Year],

                                             'IT trans cc vegacrm2'[Month],

                                             'IT trans cc vegacrm2'[Selling_point_ID],

                                             'IT trans cc vegacrm2'[Customer_ID],

                                             "Consumption", SUM ( 'IT trans cc vegacrm2'[Sale_value] )

                                   ),

                                  [Consumption]

                       ),

                     ALL ( 'IT trans cc vegacrm2'[Customer_ID] )

         )

The problem is that if I make a matrix for a given selling point, and a given month (and year), the average in some cases changes across the customers, but I don't understand why
can anybody help me?
thanks very much

Davide

[Anonymous]

AvgSales should be simply AVERAGEX( Customers, [Consumption] ), where [Consumption] is the measure [Consumption] := SUM ( 'IT trans cc vegacrm2'[Sale_value] ). The rest is done through suitable slicing.

[davide_rossi]

First of all, thank you daxer for your answer, I then tried

AvgSales = CALCULATE(

                       AVERAGEX(

                                'IT trans cc vegacrm2'[Customer_id],

                                SUM('IT trans cc vegacrm2'[Sale_value])

                                  ))

but I get the error saying that it cannot compute a single value for the column "customer_id" in the table 'it....' , and it suggests I would need some grouping factor

any idea?

[Anonymous]

[Consumption] must be a measure. Your formula does not work because SUM(...) is an expression, not a measure. If you want to use an expression, you must wrap it in CALCULATE that does the necessary context transition. Also, the outer CALCULATE is not needed when defining AvgSales. Moreover, you can't use a naked column under AVERAGEX as the first argument. You have to retrieve values of the column through a table expression. For instance, VALUES or DISTINCT.