cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

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

Accepted Solutions
Highlighted
Community Champion
Community Champion

@pbuser

 

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
Highlighted
Community Champion
Community Champion

@pbuser

 

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

Highlighted

@Vvelarde it worked. Thanks.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors