Forum Discussion
Resolver IFind X best performing area
Resolver IAnonymous does the below help?
| TeamID | Date | ID | Band |
| 1 | 01/10/2018 | 123 | 1 |
| 2 | 01/10/2018 | 321 | 2 |
| 1 | 01/10/2018 | 456 | 1 |
| 1 | 01/09/2018 | 123 | 2 |
| 2 | 01/08/2018 | 321 | 1 |
| 3 | 01/10/2018 | 456 | 1 |
| 3 | 01/10/2018 | 789 | 3 |
| 3 | 01/09/2018 | 456 | 3 |
| 3 | 01/08/2018 | 456 | 1 |
| 3 | 01/10/2018 | 789 | 1 |
This table is then joined to a standard Date Dimension and a team table that looks like the below:
| ID | Team | Area |
| 1 | T1 | S1 |
| 2 | T2 | S1 |
| 3 | T1 | S2 |
| 4 | T1 | S3 |
Hopefully this is a bit more helpful, let me know if you need anything else.
Hi BM4291,
You measure seems not works on sample data, I try to write '1+2 percent' and 'Top area' measure and it works on my side.
Maybe you can take a look at following measures:
1+2 percent =
DIVIDE (
CALCULATE ( DISTINCTCOUNT ( Table1[ID] ), Table1[Band] IN { 1, 2 } ),
CALCULATE ( DISTINCTCOUNT ( Table1[ID] ), ALLSELECTED ( Table1 ) ),
-1
)
Top Area =
VAR currArea =
SELECTEDVALUE ( Table2[Area] )
VAR temp =
TOPN (
1,
ADDCOLUMNS ( ALLSELECTED ( Table2[Area] ), "Percent", [1+2 percent] ),
[Percent], DESC
)
RETURN
IF ( CONTAINS ( temp, Table2[Area], currArea ), "Y" )
Regards,
Xiaoxin Sheng
- BM4291
Sorry, that's not quite what I'm after (although does give some food for thought).
The %1+2 needs to be based on each area rather than the whole. Hence when using my data 'S3' is always coming up as the highest because it is 100%, what I need to be able to do is exclude it, and some others from the calculation.
We can find top 1, we can find bottom 1 but it's odd that there doesn't seem to be a straight-forward way of finding the 3rd/4th/5th best etc.