Need Help DAX

[ppsmi]

Can someone please help me. I need DAX to calculate the following on Power Bi: 

 What is the median number of items ordered per month?                          

 What is the average revenue per month?                                                           

 Which country ordered the most items? Show revenue as well.    

Which country ordered the fewest items? Show revenue as well.

 Which city generated the most revenue? Show revenue as well.                               

How many trains were sold during each year?

[v-echaithra]

Hi @ppsmi,

Thank you for reaching out Microsoft Community.

I understand you need to calculate the median number of items ordered per month, average revenue per month, country that ordered the most items, country that ordered the fewest items, city that generated the most revenue and the number of trains were sold during each year.
Fisrtly ensure proper relationships exist in your data model.

Orders[OrderNumber] - orderdetails[OrderNumber]
Orders[CustomerNumber] - Customers[CustomerNumber]
orderdetails[ProductCode] - Product[ProductCode]
Product[ProductLine] - ProductLines[ProductLine]

Inorder to calculate Median create a calculated table or measure that first summarizes items per month, then computes the median.

Median_per_month =
MEDIANX ( SUMMARIZE( Orders, YEAR(Orders[orderdate]), MONTH(Orders[orderdate]), "MonthlyQty", CALCULATE(SUM(orderdetails[quantityordered]))), [MonthlyQty])

Average_Revenue_Per_Month =
AVERAGEX ( SUMMARIZE( Orders, YEAR(Orders[orderdate]), MONTH(Orders[orderdate]), "MonthlyRevenue", CALCULATE(SUM(orderdetails[quantityordered] * orderdetails[priceeach])) ), [MonthlyRevenue])

Country_Most_Items =
TOPN( 1, ADDCOLUMNS( VALUES(Customers[country]), "TotalItems", CALCULATE(SUM(orderdetails[quantityordered])), "Revenue", CALCULATE(SUM(orderdetails[quantityordered] * orderdetails[priceeach]))), [TotalItems], DESC )

Country_Fewest_Items =
TOPN( 1, ADDCOLUMNS( VALUES(Customers[country]),
"TotalItems", CALCULATE(SUM(orderdetails[quantityordered])),
"Revenue", CALCULATE(SUM(orderdetails[quantityordered] * orderdetails[priceeach]))), [TotalItems], ASC )

City_Most_Revenue =
TOPN( 1, ADDCOLUMNS( VALUES(Customers[city]), "Revenue", CALCULATE(SUM(orderdetails[quantityordered] * orderdetails[priceeach])) ), [Revenue], DESC )

Inorder to calculate trains sold during each year, Join product - orderdetails - orders to get year.
and add this below measure to a visual with Orders[orderyear].

Trains_Sold =
CALCULATE( SUM(orderdetails[quantityordered]), FILTER( 'Product', 'Product'[productline] = "Trains"))

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,
Chaithra E.

[m4ni]

Hi @ppsmi 

The first thing I would do is create a date table and link this to the date key date in your data.  I'm assuming this is Payment Date.  Having a date table will make is easier to quantify your measure over various time periods such as by month, year etc.   Make sure you have the following:

1. Date table with all date attributes required.  Month number, Month name, Month Year

2. Mark the table as Date Table

Relate your tables on the date

3. Place month Name on the page and create a simple measure in DAX. e.g. YourMeasure = MEDIAN(QtyOrdered)

Check if that gives you sensible results.  

Note  it is important you have a proper model setup with relationships to date, geography / country.

For you other measures you will needs the COUNT functions for highest / lowest orders and the SUM function for the revenue.  Please go this far intially and feedback progress.