Find X best performing area

[BM4291]

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 for the wrong things!

In short, I want to know the following.

1. Top/Bottom performing area - I have this sorted and have followed the steps here: https://community.powerbi.com/t5/Quick-Measures-Gallery/Top-1-per-category/m-p/225700
2. Top/Bottom performing area excluding some i.e. I don't want one or two areas to be included in the calculation.
3. 2nd/3rd/4th/Xth best performing area i.e. a dynamic calculation to tell me who is Xth best performing

I've tried using a modified version of the formula in 1 using RANKX but it's returning an unexpected result.

Can't share my data unfortunately but anonymised and simplified below:

The data table, call it Table1 is joined to a Team and Date dimension, Team is structured as below:

ID | Team | Area
1 | T1 | S1
2 | T2| S1
3 | T1 | S2

4 | T1 | S3

etc... For this we're just interested in the Area, not the individual teams within it.

Currently using the below measures in PBI

Staff = DISTINCTCOUNT(Table1[ID])

StaffB1 = CALCULATE([Staff], Table1[Band] = 1)

StaffB2 = CALCULATE([Staff], Table1[Band] = 2)

StaffB1B2 = [StaffB1] + [StaffB2] ... Would StaffB1B2 = CALCULATE([Staff], Table1[Band] = 1 || Table1[Band] = 2) be more performant?

%StaffB1B2 = DIVIDE([StaffB1B2], [Staff], 0)

%StaffB1B2Rank = RANKX(ALL(Teams), [%StaffB1B2])

%StaffB1B2BestArea = CALCULATE(
    LASTNONBLANK (Team[Area], 1),
    FILTER (
        TOPN(1, VALUES(Team[Area]), [%StaffB1B2], DESC), TRUE()
        )
)

%StaffB1B2BestAreaValue = CALCULATE (
[%StaffB1B2],
FILTER ( TOPN ( 1, VALUES ( Team[Area] ), [%StaffB1B2], DESC ), TRUE () )
) 

When I filter this data to the latest month (as in the screenshot), I get S3 showing as top which whilst strictly true, it's a bit of an anomaly, it shouldn't be counted in the calculations, along with a few others and what I can't get my head around is how to exclude this, or focus on the 2nd best if it (or one of the others is 1st placed)... Any ideas at all?

[v-shex-msft]

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]

@v-shex-msft 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.

[v-shex-msft]

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.