Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
thanks in advance.
Solved! Go to Solution.
@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.
@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.
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |