Percentile based on a measure & Displaying +/- 25% based on Slicer Selection

[Chipsahoy1]

Hi Everyone,

I have 2 questions I was hoping you could help me with.

The 1st question is can I create a measure to determine the percentile of a region/store/salesperson based on a measure that calculates avg sales (the avg sales is a measure that is total sales divided by total # of customers)?  See below for sample data sets. 

Region	Avg Sales
West	$17,075
East	$12,239
South	$11,704
North	$4,755

Store	Avg Sales
D	$20,067
J	$19,231
F	$17,075
I	$14,857
A	$6,867
G	$3,444
E	$2,288
C	$2,000
H	$1,500
B	$778

Salesperson	Avg Sales
Henry	$24,000
Marcus	$21,053
Horatio	$20,000
Maxine	$19,231
Steven	$8,333
Leory	$7,500
Roberto	$4,000
Rebecca	$2,778
Elliot	$2,381
Beth	$2,182
Tommy	$2,000
Amy	$1,500
Jacob	$1,304
John	$1,000
Samantha	$778
Valerie	$667
Julie	$400

I would also like to create a visual that shows which salespeople fall within +/-25% of a slicer selection.  So my 2nd question is how can I create a visual that lists the salespeople whose avg sales fall within +/-25% of the slicer selection's avg sales?  The slicer is the names of the salespeople.  I would also like this to be able to be done at a macro level such as region and store (from the above data samples).

[Ashish_Mathur]

Hi,

I am not clear about your question.  Based on the data that you have shared, show the expected result very clearly.  Also, share the raw data to work with.

[Elena_Kalina]

Hi @Chipsahoy1 

Solution for 1st question

Try using the follow measure

Avg Sales Percentile = 
VAR AllSalespeople = 
    CALCULATETABLE(
        VALUES(SalespersonTable[Salesperson]),
        REMOVEFILTERS(SalespersonTable[Salesperson])
    )
VAR TotalRows = COUNTROWS(AllSalespeople)
VAR CurrentSalesperson = SELECTEDVALUE(SalespersonTable[Salesperson])
VAR CurrentRank = 
    IF(
        NOT ISBLANK(CurrentSalesperson),
        RANKX(
            AllSalespeople,
            [AvgSales],
            CALCULATE([AvgSales], SalespersonTable[Salesperson] = CurrentSalesperson),
            DESC
        ),
        BLANK()
    )
RETURN
IF(
    NOT ISBLANK(CurrentRank),
    DIVIDE(CurrentRank - 1, TotalRows - 1),
    BLANK()
)

in my case

AvgSales = 
AVERAGE('SalespersonTable'[AVG Sales])

since I used your data in the table and as I understood description [AVG Sales] columns  are measures in your case

Solution for 2nd question

Create a Dedicated Slicer Table

Salesperson For Slicer = 
SELECTCOLUMNS(
    SalespersonTable,
    "Salesperson", SalespersonTable[Salesperson]
)

Purpose: This decouples the slicer from your main data table, preventing unwanted filtering of your visuals.

Create Measures

Selected Salesperson Avg = 
VAR SelectedPerson = SELECTEDVALUE('Salesperson For Slicer'[Salesperson])
RETURN
IF(
    NOT ISBLANK(SelectedPerson),
    CALCULATE(
        [AvgSales],
        FILTER(
            ALL(SalespersonTable),
            SalespersonTable[Salesperson] = SelectedPerson
        )
    ),
    BLANK()
)

Display Sales in Range = 
VAR SelectedAvg = [Selected Salesperson Avg]
VAR CurrentAvg = [AvgSales]
VAR LowerBound = SelectedAvg * 0.75
VAR UpperBound = SelectedAvg * 1.25
RETURN
IF(
    ISBLANK(SelectedAvg) || 
    (CurrentAvg >= LowerBound && CurrentAvg <= UpperBound),
    CurrentAvg,  // Display value
    BLANK()      // Hide from visual
)

For Chart Visual:

Show Row = 
VAR SelectedAvg = [Selected Salesperson Avg]
VAR CurrentAvg = [Avg Sales Value]
RETURN
IF(
    ISBLANK(SelectedAvg) || 
    (CurrentAvg >= SelectedAvg*0.75 && CurrentAvg <= SelectedAvg*1.25),
    1,  // Include in visual
    0   // Exclude from visual
)

[Akash_Varuna]

Hi @Chipsahoy1 For this use the RANKX function in DAX with a measure that divides the rank by the total count of rows. For filtering salespeople within ±25% of slicer selection, create measures for the lower and upper bounds (LowerBound and UpperBound) and a WithinRange measure to identify salespeople whose sales fall in this range. Use slicers for dynamic selection and filter visuals using the WithinRange measure.