Forum Discussion
Solved
DAX - AVERAGEX behaviour incomprehensible !
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 |
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.
3 Replies
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.
