Forum Discussion
Rank using multiple attribute columns and 1 value column
Hi Anonymous
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.
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
Community SupportHi Anonymous
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:
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?RegardsFilarap- v-juanli-msft
Hi Anonymous
"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