Forum Discussion

Yrstruly2021's avatar
Yrstruly2021
Icon for Helper V rankHelper V
3 years ago
Solved

Order Value per Region

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

 

average
DAX
  • v-xiaotang's avatar
    v-xiaotang
    3 years ago

    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.

1 Reply

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    3 years ago

    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.