cancel
Showing results for
Search instead for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
Super User

@achanikya , Please, check my code again

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

4 REPLIES 4
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

Helper II

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)

Helper I
Super User

@achanikya , Please, check my code again

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

## Helpful resources

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors