Forum Discussion

BM4291's avatar
BM4291
Resolver 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,

 

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

  • BM4291's avatar
    BM4291
    Resolver I
    7 years ago

    Anonymous does the below help?

     

     

    TeamIDDateIDBand
    101/10/20181231
    201/10/20183212
    101/10/20184561
    101/09/20181232
    201/08/20183211
    301/10/20184561
    301/10/20187893
    301/09/20184563
    301/08/20184561
    301/10/20187891

    This table is then joined to a standard Date Dimension and a team table that looks like the below:

     

    IDTeamArea
    1T1S1
    2T2S1
    3T1S2
    4T1S3

     

    Hopefully this is a bit more helpful, let me know if you need anything else.

    • 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
        Resolver 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.