Scatter plot using percentile lines

[Chandramouli_BI]

I created scatter plot using Percentile lines (25th, 50th, 75th & 100th) for both X & Y axes in analytics pane option in PowerBI. Now my scatter plot is into 4*4 = 16 grids. In X-axis : Average of epk, Y-axis: Count of distinct date_key column(which is nothing but service operated days). In Values: Service(depot_id + service_code concatenation). By using this scatter plot we can see the high & low performing services based on avg Epk(Earning Per KM) & Service operated days.

Client requirement is based on avg epk & Service operated days they wanted to see each grid wise services (grid1,grid2,..grid16).

For this I have created a new Grid column using the following DAX

------------------------------------------------------------------------------------

Grid =

VAR X_25th_Percentile = PERCENTILEX.INC(ServiceSummary, [Average EPK], 0.25)

VAR Y_25th_Percentile = PERCENTILEX.INC(ServiceSummary, [Service Operated Days], 0.25)

VAR X_50th_Percentile = PERCENTILEX.INC(ServiceSummary, [Average EPK], 0.50)

VAR Y_50th_Percentile = PERCENTILEX.INC(ServiceSummary, [Service Operated Days], 0.50)

VAR X_75th_Percentile = PERCENTILEX.INC(ServiceSummary, [Average EPK], 0.75)

VAR Y_75th_Percentile = PERCENTILEX.INC(ServiceSummary, [Service Operated Days], 0.75)

VAR X_100th_Percentile = MAX(ServiceSummary[Average EPK])

VAR Y_100th_Percentile = MAX(ServiceSummary[Service Operated Days])

RETURN

SWITCH(

    TRUE(),

    [Service Average EPK] <= X_25th_Percentile && [Service Distinct Date Count] <= Y_25th_Percentile, "Grid 1",

    [Service Average EPK] > X_25th_Percentile && [Service Average EPK] <= X_50th_Percentile && [Service Distinct Date Count] <= Y_25th_Percentile, "Grid 2",

    [Service Average EPK] > X_50th_Percentile && [Service Average EPK] <= X_75th_Percentile && [Service Distinct Date Count] <= Y_25th_Percentile, "Grid 3",

    [Service Average EPK] > X_75th_Percentile && [Service Average EPK] <= X_100th_Percentile && [Service Distinct Date Count] <= Y_25th_Percentile, "Grid 4",

    [Service Average EPK] <= X_25th_Percentile && [Service Distinct Date Count] > Y_25th_Percentile && [Service Distinct Date Count] <= Y_50th_Percentile, "Grid 5",

    [Service Average EPK] > X_25th_Percentile && [Service Average EPK] <= X_50th_Percentile && [Service Distinct Date Count] > Y_25th_Percentile && [Service Distinct Date Count] <= Y_50th_Percentile, "Grid 6",

    [Service Average EPK] > X_50th_Percentile && [Service Average EPK] <= X_75th_Percentile && [Service Distinct Date Count] > Y_25th_Percentile && [Service Distinct Date Count] <= Y_50th_Percentile, "Grid 7",

    [Service Average EPK] > X_75th_Percentile && [Service Average EPK] <= X_100th_Percentile && [Service Distinct Date Count] > Y_25th_Percentile && [Service Distinct Date Count] <= Y_50th_Percentile, "Grid 8",

    [Service Average EPK] <= X_25th_Percentile && [Service Distinct Date Count] > Y_50th_Percentile && [Service Distinct Date Count] <= Y_75th_Percentile, "Grid 9",

    [Service Average EPK] > X_25th_Percentile && [Service Average EPK] <= X_50th_Percentile && [Service Distinct Date Count] > Y_50th_Percentile && [Service Distinct Date Count] <= Y_75th_Percentile, "Grid 10",

    [Service Average EPK] > X_50th_Percentile && [Service Average EPK] <= X_75th_Percentile && [Service Distinct Date Count] > Y_50th_Percentile && [Service Distinct Date Count] <= Y_75th_Percentile, "Grid 11",

    [Service Average EPK] > X_75th_Percentile && [Service Average EPK] <= X_100th_Percentile && [Service Distinct Date Count] > Y_50th_Percentile && [Service Distinct Date Count] <= Y_75th_Percentile, "Grid 12",

    [Service Average EPK] <= X_25th_Percentile && [Service Distinct Date Count] > Y_75th_Percentile && [Service Distinct Date Count] <= Y_100th_Percentile, "Grid 13",

    [Service Average EPK] > X_25th_Percentile && [Service Average EPK] <= X_50th_Percentile && [Service Distinct Date Count] > Y_75th_Percentile && [Service Distinct Date Count] <= Y_100th_Percentile, "Grid 14",

    [Service Average EPK] > X_50th_Percentile && [Service Average EPK] <= X_75th_Percentile && [Service Distinct Date Count] > Y_75th_Percentile && [Service Distinct Date Count] <= Y_100th_Percentile, "Grid 15",

    [Service Average EPK] > X_75th_Percentile && [Service Average EPK] <= X_100th_Percentile && [Service Distinct Date Count] > Y_75th_Percentile && [Service Distinct Date Count] <= Y_100th_Percentile, "Grid 16",

    BLANK()

)

----------------------------------------------------

 

 

I created New Table : service wise average EPK & Service wise distinct date_key count(Operated Days) using the below DAX:

ServiceSummary =

SUMMARIZE(

    agg_route_trip,

    agg_route_trip[Service],

    "Average EPK", AVERAGE(agg_route_trip[epk]),

    "Service Operated Days", DISTINCTCOUNT(agg_route_trip[date_key])

)

 

-----------------------------------------------------------------------------

 

I connected this table to agg_route_trip table via 1 to many relationship using Service column

 

Issue I am facing:

 

Grid is working fine on the entire data. But the client requirement is month wise each grid wise services they want to see. For example on selection of july month scatter plot will automatically adjust to july month with 16 grids. Now they want to see each grid wise services. This is where I am facing the issue. On selection of any month the grid is not correctly filtering the services/some grids are not showing in the grid column even though in scatter plot all 16 grids visible 

 

 

PLEASE FIND THE SAMPLE FILES HERE:

 

https://docs.google.com/spreadsheets/d/1htO-yor7rDfpmeVCJSJt9v4A9OZ2NTtR/edit?usp=sharing&ouid=10810...

 

https://drive.google.com/file/d/15swrWPLmAgihwGV55O_4uE73SiXnRTqN/view?usp=sharing : Pbix File