Forum Discussion
Resolver IFind 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 for the wrong things!
In short, I want to know the following.
- 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
- Top/Bottom performing area excluding some i.e. I don't want one or two areas to be included in the calculation.
- 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?
4 Replies
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
BM4291Anonymous 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
