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.
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
Community SupportHi 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.