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?

[m4ni · Accepted solution]

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.

[v-echaithra]

Hi @ppsmi ,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Chaithra E.

[v-echaithra]

Hi @ppsmi ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

[v-echaithra]

Hi @ppsmi ,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Chaithra.

[v-echaithra · Accepted solution]

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 · Accepted solution]

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.