Dynamic Rank calculation based on multiple slicer selection

[Anonymous]

Hi Folks,

I have a requirement as follows:

Slicers in my report : 

Sales Person Name(A, B, ..Z)

Year (2015..2018)

Month  (Jan..Dec)

Market ( Internal, External)

Metric:

Achieved % = Achieved/Target

Based on the Achieved% each Sales Person is given a Rank and this Rank should be changed based on the multiple slicers selected. 

For Example:

Sales Person Name        Year         Month         Market    Achieved%

ABCD                             2015         Jan              Internal     73%

MNBV                            2015         Jan              Internal     69%

ABCD                            2015          Jan              External     57%

MNBV                            2015         Jan              External     53%

ABCD                            2015          Feb              Internal     88%

MNBV                           2015          Feb              Internal     95%

..............

So, based on the above data If I select filters as Sales Person: MNBV; Year:2015; Month: Feb; Market: Internal - Then I get the rank "1" as MNBV for the selected period is holding highest Achieved%. This is pretty straightforward as there is a single selection in all slicers. 

But if I select multiple values in slicers - Sales Person: MNBV; Year: 2015; Month: Jan and Feb; Market: Internal & External - Then I should be calculating the Achieved % based on the Multiple months & Markets which are selected and then calculate & display Rank based on this selection. 

Can someone please help on how I can able to achieve this? Also please feel free to suggest me with SQL script if this is easy to achieve at the backend (SQL Server) before getting the data into Power BI.

[v-jiascu-msft]

Hi @Anonymous,

Please try the formula below and check out the attached demo.

Measure =
RANKX ( ALLSELECTED ( dt3 ), CALCULATE ( SUM ( dt3[Achieved%] ) ) )

Best Regards,

Dale

[Anonymous]

Hi @v-jiascu-msft,

Thank you Dale for taking time in helping me here!

My problem is little different here. Let me try to rephrase and elaborate a little bit:

Scenario:
I wanted to display Rank of the employee based on his Target vs Achieved sales ratio(%). 

Slicers Applied:
Employee Number: 1,2,3..etc.,  [Atleast 1 value to be selected - Manadatory]

Market: Internal, External

Year: 2015, 2016, 2017, 2018 [Atleast 1 value to be selected - Manadatory]

Month: Jan, Feb,... Dec

Calculation of Target vs Achieved Ratio:
Ratio(%) = SUM(Achieved)/SUM(Target)
Case 1: When no slicer is applied -
As Employee Number and Year are mandatory, some value is already selected in the slicers. For our instance let's say Employee 8 and Year 2016 is selected. 

Now the Ratio(%) calculation should be only for Employee: 8; Year: 2016; Market: Both Internal & External; Month: All Months

Case 2: When some values are selected in each slicer - 

Let's say: Employee: 8; Year: 2016; Market: Internal; Months: Jan,Feb,Mar
Now the Ratio(%) should be calculated only based on the above filters selected. 

So, the final % here will be  - SUM(Achieved)/SUM(Target) is only calculated for Employee 8, Year 2016, Internal Market & Jan, Feb, Mar.

Once the Ratio is calculated dynamically based on the slicers selection, I need to get the rank(Single Value) to be displayed on the top of the dashboard - This should be calculated by comparing with the Ratio values of all other employees and again based on the selected filters(Year, Market & Month).

Please let me know if I am clear on my requirement (Little bit tricky to explain though 🙂 ). Thanks a ton in advance!