cancel
Showing results for
Did you mean:
Highlighted
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 id employee number sales date sales amount sales date key month 1 100806 1-May-16 1 20160501 May-16 2 100801 3-May-16 2 20160503 May-16 3 100806 8-May-16 3 20160508 May-16 4 100801 6-May-16 4 20160506 May-16 5 100807 1-Apr-16 2 20160401 Apr-16 6 100808 2-Apr-16 1 20160402 Apr-16 7 100807 3-Apr-16 4 20160403 Apr-16 8 100807 1-May-16 1 20160501 May-16 9 100808 3-May-16 2 20160503 May-16 10 100806 1-May-16 7 20160501 May-16 11 100801 3-May-16 8 20160503 May-16 12 100806 8-May-16 9 20160508 May-16 13 100806 10-May-16 10 20160501 May-16 14 100808 3-Jun-16 30 20160603 Jun-16 15 100809 3-Apr-16 2 20160403 Apr-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 number Sales Amount Apr May Sales Amount Apr Sales rank Apr May 100807 7 6 1 100808 3 1 2 100809 2 2 3

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
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
2 REPLIES 2
Highlighted
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
Highlighted
Helper IV

@Vvelarde it worked. Thanks.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### 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