topic Re: Order Value per Region in DAX Commands and Tips

Order Value per Region

Yrstruly2021 — Thu, 03 Nov 2022 12:53:53 GMT

Please assist. I want to answer this question: "What is the average order value in the African Region per month?"

My Data Model:

pbix: https://drive.google.com/file/d/1pSb2n4sHm2yxhH_m79j0ywYgWHPY0h05/view?usp=sharing

My SQL Code I tried out, please advise.

SELECT Avg(Orders) AS Order Value

FROM Orders, Customer, Nation, Region

Join Customer AS C

ON Customer.Custkey = Orders.Custkey

Join Customer AS C ON Customer.Nationkey=Nation.Nationkey

Join Nation.Nationkey=Region.Nationkey

where Region = Africa

Re: Order Value per Region

v-xiaotang — Fri, 04 Nov 2022 09:59:55 GMT

Hi Yrstruly2021

Thanks for reaching out to us.

please try this measure

Measure = var _nationkey=SELECTCOLUMNS( FILTER(REGION,REGION[R_NAME]="Africa"),"region name",[R_REGIONKEY]) var _customer= SELECTCOLUMNS( FILTER(CUSTOMER, CUSTOMER[C_NATIONKEY] in _nationkey),"custkey",[C_CUSTKEY]) var _orders= AVERAGEX(FILTER(ORDERS,ORDERS[O_CUSTKEY] in _customer),[O_TOTALPRICE]) return _orders

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.