Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
pranshu_07
Frequent Visitor

DAX for the average of 5 latest orders by the retailers based on the Order dates.

Hello all,

I've to calculate the average of the last 5 orders placed by the retailers. When I use quick measure (average per category) it shows right result for the retailers <=5 but for the retailers above 5 orders it calculate the total order average. 

Average_order = AVERAGEX ( KEEPFILTERS(VALUES('Catalog Line Item'[Order_Date])),
    CALCULATE(SUM('Catalog Line Item'[Order_value]))
)
I modified the dax for the last 5 result using top N function,
Average_Order =
AVERAGEX(
KEEPFILTERS(TOPN(5,'Catalog Line Item','Catalog Line Item'[Order_Date].[Date],DESC)),
CALCULATE(SUM('Catalog Line Item'[Order_value]))
)
The results were wrong,
Kindly request your assistance.
1 ACCEPTED SOLUTION

@achanikya , Please, check my code again

rankx(filter(Table, [retailer_ID] = earlier([retailer_ID] )), [Order_Date],,desc,dense)

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@pranshu_07 , if this not based on selected date, then you can have column rank

 

example

rankx(filter(Table, [retailer_ID] = earlier([retailer_ID] )), [Order_Date],,desc,dense)

 

and filter <=5

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Hi I Need Last 5 order Averge Sales on Outlet based . SO can you elaborate on the above DAX that after alocatting rank to the date of sale how can I calculate the average.

for eg: Condition 1: If a retailer buy 3000 rs. worth of item only 3 times this year than the average will be 3000/3=1000.

but if the other retailer had bought 3000rs. worth items 5 times this year then it should be 600.

and if he bought more than 5 times than also 600.

the sales may be continous or in a random manner like (jan, march,august) 

@achanikya , Please, check my code again

rankx(filter(Table, [retailer_ID] = earlier([retailer_ID] )), [Order_Date],,desc,dense)

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.