Latest Enterprise DNA Initiatives

Ranking with Calculated Measure and Grouping

DAX Calculations
#1

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.

image

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…

image

regards

#2

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

Thanks.

#3

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…

image

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…

image

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

#4

@sanappi09 Here you go:

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

1
11197×538 27.3 KB

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

2
2804×357 12.2 KB

SummarizeMeasurebyDimensionandRank.pbix (106.0 KB)

I hope it solves the problem.

Thanks.

#5

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 </>.
    image
  • 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.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

#6

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

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

here the table and the ranking without Region ID ,
image

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

#7

@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!!

#8

thanks Ali!

1 Like
#9

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
image

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…

image

#10

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…

Thanks much for your solutions!

#11

@sanappi09 Really happy that you found the solution for last part yourself. Can you please share the code you used so it can be used for future reference for anybody visiting this post?

Thanks!!

#12

Absolutely Ali… i am attaching the pbix file here…

Here’s the calculation column… pretty sure that this code can be optimized, and although i dint need all the columns in this calculated table, kept it there for validation purposes…

image
image1777×506 53.3 KB

Here’s that final table where i have ranking at different grains… territory ranking across nation, territory ranking across region and the region ranking
Final-SummarizeMeasurebyDimensionandRank.pbix (127.4 KB)

image
image1212×310 14.8 KB

1 Like