Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 :
Name | Group | Month | Value |
N1 | G1 | M1 | 100 |
N1 | G1 | M1 | 100 |
N1 | G1 | M2 | 150 |
N1 | G2 | M1 | 50 |
N2 | G1 | M1 | 100 |
N2 | G1 | M1 | 100 |
N2 | G1 | M2 | 150 |
N2 | G2 | M1 | 50 |
N3 | G1 | M1 | 100 |
N3 | G1 | M2 | 150 |
N3 | G2 | M1 | 50 |
N4 | G1 | M1 | 100 |
N4 | G1 | M2 | 150 |
N4 | G2 | M1 | 50 |
Output would like something like this:
Name | Group | Month | Rank |
N1 | G1 | M1 | 1 |
N1 | G1 | M2 | 1 |
N2 | G1 | M1 | 1 |
N2 | G1 | M2 | 1 |
N3 | G1 | M1 | 1 |
N3 | G1 | M2 | 1 |
N4 | G1 | M1 | 1 |
N4 | G1 | M2 | 1 |
Filarap
Solved! Go to 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
)
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,
Your expected result in the first post is confusing. Why should there be a rank 1 for all rows? Please explain.
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.
@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 | ||||
Name | Group | Month | What I have now | What I need |
N1 | G1 | M1 | 202 | 1 |
N1 | G2 | M1 | 213 | 2 |
N1 | G1 | M2 | 215 | 1 |
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
)
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 month | Measure 2 | Measure 1 | ||
Name | Group | Month | Rank | Sum% |
John Smith (IDC123456) | Name_of_the_group one | October 19 | 1 | 202 |
John Smith (IDC123456) | Name_of_the_group one | November 19 | 1 | 8.85 |
John Smith (IDC123456) | Name_of_the_group two | November 19 | 1 | 38.22 |
This is rank functon used:
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.
Name | Group | Day | Value |
Smith, John (ABC354637) | Group_Name_One | 01/10/2019 00:00 | 6983 |
Smith, John (ABC354637) | Group_Name_One | 02/10/2019 00:00 | 11360 |
Smith, John (ABC354637) | Group_Name_One | 03/10/2019 00:00 | 14902 |
Smith, John (ABC354637) | Group_Name_One | 04/10/2019 00:00 | 15090 |
Smith, John (ABC354637) | Group_Name_One | 07/10/2019 00:00 | 25042 |
Smith, John (ABC354637) | Group_Name_One | 08/10/2019 00:00 | 17645 |
Smith, John (ABC354637) | Group_Name_One | 09/10/2019 00:00 | 17550 |
Smith, John (ABC354637) | Group_Name_One | 10/10/2019 00:00 | 15182 |
Smith, John (ABC354637) | Group_Name_One | 11/10/2019 00:00 | 13098 |
Smith, John (ABC354637) | Group_Name_One | 14/10/2019 00:00 | 15150 |
Smith, John (ABC354637) | Group_Name_One | 15/10/2019 00:00 | 12513 |
Smith, John (ABC354637) | Group_Name_One | 16/10/2019 00:00 | 10658 |
Smith, John (ABC354637) | Group_Name_One | 17/10/2019 00:00 | 18816 |
Smith, John (ABC354637) | Group_Name_One | 18/10/2019 00:00 | 15220 |
Smith, John (ABC354637) | Group_Name_One | 21/10/2019 00:00 | 15322 |
Smith, John (ABC354637) | Group_Name_One | 22/10/2019 00:00 | 7945 |
Smith, John (ABC354637) | Group_Name_One | 23/10/2019 00:00 | 11934 |
Smith, John (ABC354637) | Group_Name_One | 24/10/2019 00:00 | 12130 |
Smith, John (ABC354637) | Group_Name_One | 25/10/2019 00:00 | 6652 |
Smith, John (ABC354637) | Group_Name_One | 28/10/2019 00:00 | 12829 |
Smith, John (ABC354637) | Group_Name_One | 29/10/2019 00:00 | 16205 |
Smith, John (ABC354637) | Group_Name_One | 30/10/2019 00:00 | 12697 |
Smith, John (ABC354637) | Group_Name_One | 31/10/2019 00:00 | 11126 |
Smith, John (ABC354637) | Group_Name_One | 01/11/2019 00:00 | 10599 |
Smith, John (ABC354637) | Group_Name_One | 05/11/2019 00:00 | 12426 |
Smith, John (ABC354637) | Group_Name_One | 06/11/2019 00:00 | 13580 |
Smith, John (ABC354637) | Group_Name_Two | 11/11/2019 00:00 | 16004 |
Smith, John (ABC354637) | Group_Name_Two | 12/11/2019 00:00 | 11148 |
Smith, John (ABC354637) | Group_Name_Two | 13/11/2019 00:00 | 11748 |
Smith, John (ABC354637) | Group_Name_Two | 14/11/2019 00:00 | 15097 |
Smith, John (ABC354637) | Group_Name_Two | 15/11/2019 00:00 | 17009 |
Smith, John (ABC354637) | Group_Name_Two | 18/11/2019 00:00 | 12389 |
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
)
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.