Dynamic Quartiles/Percentiles in Visual

[EshaanG]

Hello All,

I have three tables :

People Table : Email IDs with people attributes like Country/Geography etc.

Training Table : Email IDs with Training Details like Training ID, Duration, Training Type

Sales Table : Email IDs with Sales Details like Deal ID, Deal Value, Deal Type etc.

I am looking to classify everyone in the People Table into Top 25 & Bottom 75 quartiles based on sum of Duration in the Training Table and make it dynamic based on slicers applied on both People & Training Tables. Subsequently I am trying to link this dynamic classification to the Sales Table to visualize how the quartiles perform (For e.g. Do Top 25 have more deals?)

I understand that calculated columns won't work as they are dynamic and measures cannot be used as a dimension in the visual. Any thoughts on how I can solve this would be highly appreciated!

[Ashish_Mathur]

Hi,

I am not sure how much i can help but i would like to try.  Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[EshaanG]

Thanks a lot - It seems that the Training Classification is not dynamic as everyone gets classified as Top 25%. Is this expected behavior? Also, the PERCENTILEX should be .INC or .EXC as I am using .INC for the below results

[Kedar_Pande]

Total Training Duration = 
CALCULATE(
SUM(Training[Duration]),
FILTER(Training, Training[Email ID] = SELECTEDVALUE(People[Email ID]))
)

Top 25% Threshold = 
PERCENTILEX(
VALUES(People[Email ID]), 
[Total Training Duration], 
0.75
)

Bottom 75% Threshold = 
PERCENTILEX(
VALUES(People[Email ID]), 
[Total Training Duration], 
0.25
)

  Training Classification = 
VAR TotalDuration = [Total Training Duration]
VAR Top25 = [Top 25% Threshold]
VAR Bottom75 = [Bottom 75% Threshold]
RETURN
IF(
ISBLANK(TotalDuration),
BLANK(),
IF(
TotalDuration >= Top25,
"Top 25%",
IF(
TotalDuration < Bottom75,
"Bottom 75%",
"Middle 50%"
)
)
)

Total Deals = 
CALCULATE(
COUNT(Sales[Deal ID]),
FILTER(Sales, Sales[Email ID] = SELECTEDVALUE(People[Email ID]))
)

Total Deal Value = 
CALCULATE(
SUM(Sales[Deal Value]),
FILTER(Sales, Sales[Email ID] = SELECTEDVALUE(People[Email ID]))
)

Example Visualization
Create a bar chart with:
Axis: Training Classification
Values: Total Deals and Total Deal Value