DAX - AVERAGEX behaviour incomprehensible !

[informer]

Hi,

2 tables 

Sales rows (please don't focus on values of transaction_id, no consequences on my question)

Calandar

I calculated 

Turnover = SUMX('Sales rows', 'Sales rows'[quantity_sold] * 'Sales rows'[unit_price])

Average Daily Turnover EXPR = AVERAGEX(VALUES(dCalendar[transaction_date]), SUMX ('Sales rows','Sales rows'[quantity_sold] * 'Sales rows'[unit_price]))

Average Daily Turnover MESURE = AVERAGEX(VALUES(dCalendar[transaction_date]),[Turnover])

And Average Daily Turnover values on charts are just a nonsense because all of them are same with wrong values !

I expected for Average Daily Turnover for 

• 01 january 2019 : 10 = (2*2 + 1*2 + 8*3 )/3 but the result is 30
• 01 february 2019 : 118= (6*9 + 15*30)/3 but the result is 354

Any idea for helping me to understand the charts belows ?

Thanks by advanvce for any help !

transaction_id	OrderDate	store_id	customer_id	product_id	quantity_sold	unit_price	staff_id
390	01/01/2019	5	5658	22	2	2	12
1228	01/01/2019	5	5197	22	1	2	30
1644	02/01/2019	5	5247	22	8	3	12
390	02/01/2019	5	5658	22	6	9	12
1228	02/01/2019	5	5197	22	20	15	30

Calandar

Transaction_Date

01/01/2019

02/01/2019

03/01/2019

04/01/2019

05/01/2019

06/01/2019

07/01/2019

08/01/2019

09/01/2019

10/01/2019

11/01/2019

12/01/2019

13/01/2019

14/01/2019

15/01/2019

16/01/2019

17/01/2019

18/01/2019

19/01/2019

20/01/2019

21/01/2019

22/01/2019

23/01/2019

24/01/2019

25/01/2019

26/01/2019

27/01/2019

28/01/2019

29/01/2019

30/01/2019

31/01/2019

01/02/2019

02/02/2019

03/02/2019

04/02/2019

05/02/2019

06/02/2019

07/02/2019

08/02/2019

09/02/2019

10/02/2019

11/02/2019

12/02/2019

13/02/2019

14/02/2019

15/02/2019

16/02/2019

17/02/2019

18/02/2019

19/02/2019

20/02/2019

21/02/2019

22/02/2019

23/02/2019

24/02/2019

25/02/2019

26/02/2019

27/02/2019

28/02/2019

[Irwan · Accepted solution]

hello @informer 

please check if this accomodate your need.

i am not sure the result doesnt match to what you state above.

Based on your sample data above,

1-Jan should be 3 as (2*2+1*2)/2

2-Jan should be 126 (8*3+6*9+20*15)/3

create a measure with following DAX.

Average =
AVERAGEX(
    'Table',
    'Table'[quantity_sold]*'Table'[unit_price]
)

Hope this will help.
Thank you.

[informer]

Hi Irwan,
Thanks so much for your help and solution which is absolutly strategic for me. 👍🤜🤛

You make my day 😎

[Irwan]

Hello @informer 

Glad to be a help.

Thank you.