Ranking with multiple columns

[Anonymous]

I have the below data and I need to calculate the 3 red highlighted colums. I was able to get Salesrep ranking with Rankx(THis is all data ranking). But others are not working within all this data columns together. I even tried to remove some data and created dimension tables and then did crossjoin but still its not working(maybe I didnt do correct). Is it even possible to create a table like below. Please guide.

This image is just a sample data, data is more with all duplicate values in Region, Super Region,President and Sales Director. Salesrep names are unique.

[v-angzheng-msft · Accepted solution]

Hi, @Anonymous 

Measures:

_Super Region Ranking = 
IF([_Total Services]<=0,0,RANKX(FILTER(ALL('Table2'),'Table2'[ Super Region ]=MAX('Table2'[ Super Region ])&&[_Total Services]>0),[_Total Services],,DESC,Dense))

_Region Ranking = 
IF([_Total Services]<=0,0,RANKX(FILTER(ALL('Table2'),'Table2'[Region ]=MAX('Table2'[Region ])&&[_Total Services]>0),[_Total Services],,DESC,Dense))

_Total Services = SUM('Table2'[ Security Total  ])

Result:

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello,

I actually need to create other reports in a similar way so I was duplicating it, but cannot understand what you did, Is there an article explaining it? I need to understand below code(mainly the role of MAX,why and what is that doing here). Thank you so much

RANKX(

FILTER(

ALL('Security Data'),

'Security Data'[Region]=Max('Security Data'[Region])&& [Total Services]>0),

[Total Services],,DESC,Dense

[Anonymous]

Thank you, it works.

[Anonymous]

Thanks for your reply, As mentioned in my reply, I had already achieved company ranking(sales rep ranking). I need help with other two, as mentioned earlier "Need help for Super Region Ranking, Region Ranking ". 

[v-angzheng-msft · Accepted solution]

Hi, @Anonymous 

Measures:

_Super Region Ranking = 
IF([_Total Services]<=0,0,RANKX(FILTER(ALL('Table2'),'Table2'[ Super Region ]=MAX('Table2'[ Super Region ])&&[_Total Services]>0),[_Total Services],,DESC,Dense))

_Region Ranking = 
IF([_Total Services]<=0,0,RANKX(FILTER(ALL('Table2'),'Table2'[Region ]=MAX('Table2'[Region ])&&[_Total Services]>0),[_Total Services],,DESC,Dense))

_Total Services = SUM('Table2'[ Security Total  ])

Result:

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello,

I actually need to create other reports in a similar way so I was duplicating it, but cannot understand what you did, Is there an article explaining it? I need to understand below code(mainly the role of MAX,why and what is that doing here). Thank you so much

RANKX(

FILTER(

ALL('Security Data'),

'Security Data'[Region]=Max('Security Data'[Region])&& [Total Services]>0),

[Total Services],,DESC,Dense

[v-angzheng-msft · Accepted solution]

Hi, @Anonymous 

The max function here is used to get the value of the current row. In the current row context, since there is only one row, no matter you use the MAX, MIN, or SELECTEDVALUE functions, it has the same effect.

Now to explain the above formula, first we look at the syntax of the RANKX function:

So

_Super Region Ranking =
IF(
    [_Total Services] <= 0,
    0,
    RANKX(
        FILTER(
            ALL( 'Table2' ),
            'Table2'[ Super Region ] = MAX( 'Table2'[ Super Region ] )
                && [_Total Services] > 0
        ),
//Get all records in the same region and rank them in a sub-table of all rows in this same region.
//Filter the sub-tables whose region in the entire table is equal to the region of the current row.
        [_Total Services],
        ,
        DESC,
        DENSE
    )
)

Hope this helps you understand.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you

[Anonymous]

Thank you, it works

[Anonymous]

Thanks for your reply. I need ranking by " security total" .  Below is the sample data with results in bold columns. Yes region is a subset for super region and it is ranking within each region. Kindly note that this is just a sample. The actual data is not like this i.e here each super region has four rows only but in acual data it is not fixed(East has 20 rows, south 50 rows etc), same goes for region, here region ranking is 1-2 but in actual data VA occurs 10 times so its ranking is 1-10. Also for -ve and 0 values in " security total", rank is 0. I was able to achieve Salesrep ranking with  - 

Company Ranking = IF(OR('Calculations'[Total Services]=0,'Calculations'[Total Services]<0),0,RANKX(ALL('Security Data'),[Total Services])).

Need help for Super Region Ranking, Region Ranking 

Region	Super Region	President	Sales Director	Super Region Ranking	Region Ranking	SalesEep Ranking	SalesRep Name	Security Total	total Spend	% Security Spend
CA	West	A3	Tan	3	1	5	Rob	306,614	10,937	8%
CA	West	A3	Tan	4	2	6	Emma	246,627	10,937	5486%
FL	South	A6	Med	2	1	8	Charlotte	126,653	779,857	1%
FL	South	A6	Med	4	2	10	Jose	6,679	779,857	1%
GA	South	A10	Ryan	1	1	7	Mia	186,640	779,857	1%
GA	South	A10	Ryan	3	2	9	Sam	66,666	779,857	1%
MD	East	A2	Mill	2	1	2	Sophia	800,000	212,853	376%
MD	East	A2	Mill	3	2	13	Dave	289	212,853	0%
NYC	North	A4	Bro	2	1	12	John	1,633	484,674	0%
NYC	North	A4	Bro	0	0	0	Ava	(3,100)	484,674	0%
PA	North	A9	Brown	1	1	11	Isabella	4,000	484,674	1%
PA	North	A9	Brown	0		0	Kevin	(734)	484,674	0%
TX	West	A8	Tand	1	1	3	Amelia	426,588	10,937	8%
TX	West	A8	Tand	2	2	4	Ram	366,601	10,937	8%
VA	East	A1	Mil	1	1	1	Olivia	3,000,000	212,853	1409%
VA	East	A1	Mil	4	2	14	Nick	287	212,853	0%

[v-angzheng-msft]

Hi, @Anonymous 

Do you want to get the ranking based on the Security Total value or the total Spend value? Notice that a region appears multiple times in the Super Region column, how to calculate their ranking? Is the % security spend column also the column that you want to add?

Could you please consdier sharing more details about it and posting expected result so it is clear on what needs to be implemented?  And It would be great if there is a sample file without any sesentive information here.  

It makes it easier to give you a solution.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello, I did replied to this post, not sure what happened to my reply. Sending you the sample data with results needed(bold cols). I want to rank with Security total only. The ones with 0 or -ve amount in security total has rank 0. I did correctly for salesrep ranking, Company Ranking = IF(OR('Calculations'[Total Services]=0,'Calculations'[Total Services]<0),0,RANKX(ALL('Security Data'),[Total Services])) Total Services is a measure for sum of security total.

Region	Super Region	President	Sales Director	Super Region Ranking	SalesEep Ranking	SalesRep Name	Security Total	total Spend	% Security Spend
CA	West	A3	Tan	3	5	Rob	306,614	10,937	8%
CA	West	A3	Tan	4	6	Emma	246,627	10,937	5486%
FL	South	A6	Med	2	8	Charlotte	126,653	779,857	1%
FL	South	A6	Med	4	10	Jose	6,679	779,857	1%
GA	South	A10	Ryan	1	7	Mia	186,640	779,857	1%
GA	South	A10	Ryan	3	9	Sam	66,666	779,857	1%
MD	East	A2	Mill	2

[v-angzheng-msft]

Hi, @Anonymous 

Try to create a measure like this:

Company Ranking = 
IF([Total Services]<=0,0,RANKX(FILTER(ALL('Table'),'Table'[ Security Total  ]>0),[Total Services],,ASC,Dense))

Result:

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks for your reply, As mentioned in my reply, I had already achieved company ranking(sales rep ranking). I need help with other two, as mentioned earlier "Need help for Super Region Ranking, Region Ranking ".