Forum Discussion
Resolver IFind X best performing area
Hi BM4291,
I'd like some sample data with expected result for test and coding formula, it is hard to coding formula from snapshot.
Notice: some of fake data with same data structure and relationship.
Regards,
Xiaoxin Sheng
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.