topic Re: AVERAGEX not fixed result in DAX Commands and Tips

AVERAGEX not fixed result

Anonymous — Mon, 22 Feb 2021 10:41:20 GMT

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

Re: AVERAGEX not fixed result

Anonymous — Mon, 22 Feb 2021 11:52:47 GMT

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.

Re: AVERAGEX not fixed result

Anonymous — Mon, 22 Feb 2021 14:47:24 GMT

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?

Re: AVERAGEX not fixed result

Anonymous — Mon, 22 Feb 2021 16:45:20 GMT

[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.