Forum Discussion
EshaanG
Helper I
Helper IDynamic Quartiles/Percentiles in Visual
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, Trai...
Kedar_Pande
Super User
Super UserTotal 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