# Ranking with Calculated Measure and Grouping

Hello to EDNA team and its members… here with another question related to DAX…

I have a slightly complex requirement, so appreciate your patience in having to read through it all.

I am trying to derive ranking based off a calculated measure - Annual forecast payout for reach rep and each rep has regional hierarchy… What i need to rank Reps within a region based on their annual forecasted payout (which is a calculated measure).

Annual Forecast is calculated by looking at the rep’s attainment for a month and applying payout factor to it based on their attainment… Here’s an example - Rep A, Sales 110, Quota 100, the attainment is 110%, so payout curve for 110% is 30%… So Rep in a year, if they hit quota are guaranteed to make 40K, so 40k *1.30 (payout curve factor) is \$52,000…

So data looks like, where i have a territory rank calculated based off of YTD Projection.

What i am also looking for is to rank Reps (Territory) within a Region (AreaCode), so can see the territory ranking within a region like the image below…

regards

@sanappi09 It’s totally achievable. What we need is a sample of PBIX file to work with.

Thanks.

thanks a Ton in advance Ali… I have attached a sample pbix file here, where you can see the calculation measure - Year End Projection…

So on repot 1, looking to rank territory within a region based on -‘Year End Projection’ field in the image below…

From the image above, territory ID 014 will get rank 1 since it has the highest Year End Projection, Ter ID - 21 will be Rank 2…
Rank will need to get reset for each region id, so in Region ID 02, ter id 029 will be rank #1, as it has the highest Year End Projection…SummarizeMeasurebyDimensionandRank.pbix (105.3 KB)

And then in second report, i need to create a rank by Region also based on Year End Projection field…

so from the above image, region ID 03 will have a rank #1, region id 04 will have a rank of 2 etc, based on their Year End Projection

@sanappi09 Here you go:

``````Rank Territory within Region =
RANKX(
ALL(QuotawithSales[Territory]),
[YearEnd Projection])
``````

``````Region Rank =
VAR annualpayout =
SUMX ( QuotawithSales, [YearEnd Projection] )
RETURN
RANKX ( ALL ( QuotawithSales[Region] ), [YearEnd Projection],, DESC )
``````

SummarizeMeasurebyDimensionandRank.pbix (106.0 KB)

I hope it solves the problem.

Thanks.

Hi @sanappi09 g posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

• Use the forum search to discover if your query has been asked before by another member.
• When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.
• Use the proper category that best describes your topic
• Provide as much context to a question as possible.
• Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Hi Ali, thanks, the solution works fine only if i have region id in the table…

Once i remove the region ID, the rank does not get applied…

here the table and the ranking without Region ID ,

so is it possible to apply the ranking on territory without region id in the table…

@sanappi09

Yes , sure. Here you go:

``````Rank Territory without RegionID =
CALCULATE(
RANKX(
ALL(QuotawithSales[Territory]),
[YearEnd Projection]),
VALUES(QuotawithSales[Region]))
``````

Check the Third Page in the file.

SummarizeMeasurebyDimensionandRank.pbix (106.6 KB)
Thanks!!

thanks Ali!

1 Like

Hi Ali, sorry to come back again with one final question related to ranking, have already learnt a lot, so appreciate your inputs on this one more question…

Is it possible to retain the actual regional ranking while looking at the data for an individual territory…

This table shows region ID rank is 5 based on Year end Projection

so need to see the region rank (against all regions) in a page that will be filtered on territory.

Here below, i have data filtered for territory ID 012 which belongs to Region ID 01 (with the rank of 5 based on the earlier image), so expecting to see 5 as the region rank for all the territory that belongs to Region 01, All the territories that belong to Region ID 02, would need to show Rank#4…

Hi Ali, i was able to figure out pulling in the ranking of a region (while territory being filter)… had to create a summarize table and apply rank in it… then used related function to pull in the regional ranking of a territory…