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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

DAX experts required - Rankx for total sales Apr & May but only for those has sales in Apr

A simple sales table like this.

sales idemployee numbersales datesales amountsales date keymonth
11008061-May-16120160501May-16
21008013-May-16220160503May-16
31008068-May-16320160508May-16
41008016-May-16420160506May-16
51008071-Apr-16220160401Apr-16
61008082-Apr-16120160402Apr-16
71008073-Apr-16420160403Apr-16
81008071-May-16120160501May-16
91008083-May-16220160503May-16
101008061-May-16720160501May-16
111008013-May-16820160503May-16
121008068-May-16920160508May-16
1310080610-May-161020160501May-16
141008083-Jun-163020160603Jun-16
151008093-Apr-16220160403Apr-16

 

Need to create a top N sales by employee in Apr & May but only for those employees who has sales in Apr.

 

Here what I did,

Sales Amount Apr = CALCULATE(sum(Sales[sales amount]), month(Sales[Sales Date]) = 4)

 

Sales Amount Apr May = CALCULATE(sum(Sales[sales amount]), month(Sales[Sales Date]) >= 4    && month(Sales[Sales Date]) <= 5

 

Sales rank Apr May = rankx(ALLSELECTED(Sales[employee number]), Sales[Sales Amount Apr May])

 

Then I got this in a table (with a visual filter [Sales Amount Apr]>0)

 

employee numberSales Amount Apr MaySales Amount AprSales rank Apr May
100807761
100808312
100809223

 

Working as expected. Correct right?

However, I need to display top N. That is the reason to introduce the rank measure. If I apply a visual filter Sale Rank Apr May <=2 (EG to display only the top 2), nothing will display. It only works if I change it to Sale Rank Apr May <=5. So something wrong with the rank measure.

 

What have I done wrong? and how do I achieve my outcome?

 

thanks in advance.

 

1 ACCEPTED SOLUTION
Vvelarde
Community Champion
Community Champion

@Anonymous

 

You can obtain the results by this way:

 

Change Sales Amount Apr May to this:

 

Sales Amount Apr May = If (Sales[Sales Amount Apr]>0,CALCULATE(sum(Sales[sales amount]), month(Sales[Sales Date]) >= 4    && month(Sales[Sales Date]) <= 5),BLANK())

Change Sales Rank Apr May to this:

 

Sales rank Apr May = If(Sales[Sales Amount Apr May]>0,rankx(ALLSELECTED(Sales[employee number]),Sales[Sales Amount Apr May]),BLANK())

 

There is no need to a visual filter [Sales Amount Apr]>0) . Only the sales Rank Apr May visual filter to obtain the desire TOPN.

 




Lima - Peru

View solution in original post

2 REPLIES 2
Vvelarde
Community Champion
Community Champion

@Anonymous

 

You can obtain the results by this way:

 

Change Sales Amount Apr May to this:

 

Sales Amount Apr May = If (Sales[Sales Amount Apr]>0,CALCULATE(sum(Sales[sales amount]), month(Sales[Sales Date]) >= 4    && month(Sales[Sales Date]) <= 5),BLANK())

Change Sales Rank Apr May to this:

 

Sales rank Apr May = If(Sales[Sales Amount Apr May]>0,rankx(ALLSELECTED(Sales[employee number]),Sales[Sales Amount Apr May]),BLANK())

 

There is no need to a visual filter [Sales Amount Apr]>0) . Only the sales Rank Apr May visual filter to obtain the desire TOPN.

 




Lima - Peru
Anonymous
Not applicable

@Vvelarde it worked. Thanks.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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