Forum Discussion

EshaanG's avatar
EshaanG
Icon for Helper I rankHelper I
1 year ago

Dynamic 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, 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!

 

 

Need Help
Show and Tell

3 Replies

  • Kedar_Pande's avatar
    Kedar_Pande
    Icon for Super User rankSuper User
    1 year ago
    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

     

  • EshaanG's avatar
    EshaanG
    Icon for Helper I rankHelper I
    1 year ago

    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

     

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    1 year ago

    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.