Forum Discussion

BM4291's avatar
BM4291
Icon for Resolver I rankResolver I
7 years ago

Find X best performing area

Hi,   Struggling with something at the moment and having scoured the web I keep finding myself going over the same articles and not getting any closer to where I would like to be... Maybe searching...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
BM4291
Icon for Resolver I rankResolver I
7 years ago

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.