Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
filarap
Helper III
Helper III

Rank using multiple attribute columns and 1 value column

Hi all,

 

Maybe i am stupid, but i am strugling with Ranking formula quite a bit and was not able to find solution afer looking into various online rescources.

 

I have dataset with various people, working in different groups during a month. I need to rank their time per group each month (1,2,3) and then use filters to use , ex. only number 1 ranks.

 

I have tried various examples of calculate, rankx, allselected, allexcept, but nothing gave me what i needed. Please help!

 

Dataset looks something like this :

 

NameGroupMonthValue
N1G1M1100
N1G1M1100
N1G1M2150
N1G2M150
N2G1M1100
N2G1M1100
N2G1M2150
N2G2M150
N3G1M1100
N3G1M2150
N3G2M150
N4G1M1100
N4G1M2150
N4G2M150

 

Output would like something like this:

 

NameGroupMonthRank
N1G1M11
N1G1M21
N2G1M11
N2G1M21
N3G1M11
N3G1M21
N4G1M11
N4G1M21

 

Filarap

1 ACCEPTED SOLUTION

Hi @filarap 

Create a calcualted column,

month-year = FORMAT([Day],"Mmmm YYYY")

Then create two measures

sum =
CALCULATE (
    SUM ( 'Table 3'[Value] ),
    FILTER (
        ALLSELECTED ( 'Table 3' ),
        'Table 3'[Name] = MAX ( 'Table 3'[Name] )
            && 'Table 3'[month-year] = MAX ( 'Table 3'[month-year] )
            && 'Table 3'[Group] = MAX ( 'Table 3'[Group] )
    )
)

rank =
RANKX (
    FILTER (
        ALLSELECTED ( 'Table 3' ),
        'Table 3'[month-year] = MAX ( 'Table 3'[month-year] )
            && 'Table 3'[Name] = MAX ( 'Table 3'[Name] )
    ),
    [sum],
    ,
    DESC,
    DENSE
)

Capture1.JPGCapture2.JPG

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

11 REPLIES 11
Ashish_Mathur
Super User
Super User

Hi,

Your expected result in the first post is confusing.  Why should there be a rank 1 for all rows?  Please explain.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur 

 

In final output, i considered that i have already filtered a table to show only rank 1.

Goal is to see only highest value group per person per month.

 

Hope it makes it clearer

Filarap

Hi,

Based on the first Table that you have shared in your first post, please show the exact rank that you are expecting.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

@filarap Please create a calculated column 

Column = 
VAR _month ='Table'[Month]
RETURN RANKX(FILTER('Table','Table'[Month]=_month),'Table'[Value],,,Dense)

Thank you vimal_parmar,

 

I now have ranking based on a month, but i am missing it on name and group level. How can i add other dimensions?

 

   Rank
NameGroupMonthWhat I have nowWhat I need
N1G1M12021
N1G2M12132
N1G1M22151

 

Regards

Filarap

Hi @filarap 

Create two measures

Measure = SUM('Table 2'[Value])

Measure 2 =
RANKX (
    FILTER (
        ALLSELECTED ( 'Table 2' ),
        'Table 2'[Month] = MAX ( 'Table 2'[Month] )
            && 'Table 2'[Name] = MAX ( 'Table 2'[Name] )
    ),
    [Measure],
    ,
    DESC,
    DENSE
)

Capture7.JPG

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Hi Maggie and thank you,

 

Not sure if I did something wrong or this is due to data formats i have.

 

This is result i got (i have also changed format to match what i have):

 

  Format is 1st day of monthMeasure 2Measure 1
NameGroupMonthRankSum%
John Smith (IDC123456)Name_of_the_group oneOctober 191202
John Smith (IDC123456)Name_of_the_group oneNovember 1918.85
John Smith (IDC123456)Name_of_the_group twoNovember 19138.22

 

This is rank functon used:

 

rank = RANKX (
FILTER (
ALLSELECTED ('table name'),
'table name'[Month] = MAX('table name'[Month])
&& 'table name'[Name]= MAX ('table name'[Name])
),
[sum%],
,
DESC,
DENSE
)
 
What did i do wrong?
Regards
Filarap

Hi @filarap 

"Format is 1st day of month",

How do you format the month? 

Use Format function or anything else?

 

How do the [sum%] calculate?

 

I can't reproduce your problem,

Could you share a sample data for me to test?

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Hi @v-juanli-msft Maggie

 

I meant that i used startofmonth to get it and then formated to mmmm-yy using modeling tab. So it is not test in data i showed here. 1/10/2019 to October 2019

 

Sum% is simple sum, % is my own naming convention. Tried with 2 different number columns and same results in the end.

sum% = sum('Agent Util'[Column])

 

Please see sample of data as i have it. I am not allowed to share real data, but format is the same. Also, i assume higher sample of data would not make a difference.

 

 

 

NameGroupDayValue
Smith, John (ABC354637)Group_Name_One01/10/2019 00:006983
Smith, John (ABC354637)Group_Name_One02/10/2019 00:0011360
Smith, John (ABC354637)Group_Name_One03/10/2019 00:0014902
Smith, John (ABC354637)Group_Name_One04/10/2019 00:0015090
Smith, John (ABC354637)Group_Name_One07/10/2019 00:0025042
Smith, John (ABC354637)Group_Name_One08/10/2019 00:0017645
Smith, John (ABC354637)Group_Name_One09/10/2019 00:0017550
Smith, John (ABC354637)Group_Name_One10/10/2019 00:0015182
Smith, John (ABC354637)Group_Name_One11/10/2019 00:0013098
Smith, John (ABC354637)Group_Name_One14/10/2019 00:0015150
Smith, John (ABC354637)Group_Name_One15/10/2019 00:0012513
Smith, John (ABC354637)Group_Name_One16/10/2019 00:0010658
Smith, John (ABC354637)Group_Name_One17/10/2019 00:0018816
Smith, John (ABC354637)Group_Name_One18/10/2019 00:0015220
Smith, John (ABC354637)Group_Name_One21/10/2019 00:0015322
Smith, John (ABC354637)Group_Name_One22/10/2019 00:007945
Smith, John (ABC354637)Group_Name_One23/10/2019 00:0011934
Smith, John (ABC354637)Group_Name_One24/10/2019 00:0012130
Smith, John (ABC354637)Group_Name_One25/10/2019 00:006652
Smith, John (ABC354637)Group_Name_One28/10/2019 00:0012829
Smith, John (ABC354637)Group_Name_One29/10/2019 00:0016205
Smith, John (ABC354637)Group_Name_One30/10/2019 00:0012697
Smith, John (ABC354637)Group_Name_One31/10/2019 00:0011126
Smith, John (ABC354637)Group_Name_One01/11/2019 00:0010599
Smith, John (ABC354637)Group_Name_One05/11/2019 00:0012426
Smith, John (ABC354637)Group_Name_One06/11/2019 00:0013580
Smith, John (ABC354637)Group_Name_Two11/11/2019 00:0016004
Smith, John (ABC354637)Group_Name_Two12/11/2019 00:0011148
Smith, John (ABC354637)Group_Name_Two13/11/2019 00:0011748
Smith, John (ABC354637)Group_Name_Two14/11/2019 00:0015097
Smith, John (ABC354637)Group_Name_Two15/11/2019 00:0017009
Smith, John (ABC354637)Group_Name_Two18/11/2019 00:0012389

 

Regards

Filarap

Hi @filarap 

Create a calcualted column,

month-year = FORMAT([Day],"Mmmm YYYY")

Then create two measures

sum =
CALCULATE (
    SUM ( 'Table 3'[Value] ),
    FILTER (
        ALLSELECTED ( 'Table 3' ),
        'Table 3'[Name] = MAX ( 'Table 3'[Name] )
            && 'Table 3'[month-year] = MAX ( 'Table 3'[month-year] )
            && 'Table 3'[Group] = MAX ( 'Table 3'[Group] )
    )
)

rank =
RANKX (
    FILTER (
        ALLSELECTED ( 'Table 3' ),
        'Table 3'[month-year] = MAX ( 'Table 3'[month-year] )
            && 'Table 3'[Name] = MAX ( 'Table 3'[Name] )
    ),
    [sum],
    ,
    DESC,
    DENSE
)

Capture1.JPGCapture2.JPG

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you @v-juanli-msft  Maggie,

 

This has solved my issue, really great!!!

 

Kind regrads

Filarap

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.