Dynamic Percentile lines in scatter plot visual no...

Chandramouli_BI · ‎11-27-2023

BELOW IS THE EXACT REQUIREMENT FROM CLIENT

I have created a scatter plot with 16 grids(percentile lines 25th,50th,75th & 100th for both X & Y axes in PowerBI analytics pane option). In X-axis service wise average EPK & in Y-axis service operated days(Which is count of distinct date column service wise). The requirement is Client wants to check services run less than 10 days(For ex) in a particular month (Ex July). For this requirement I have added month slicer & also avg EPK & service operated days(count of distinct date). Both service wise Avg EPK & service wise operated days is not working proprly with month filter. Based on month & count of distinct date slicers the scatter plot should filter the data. Now month wise is working as per your given solution.

Below are the DAX I used:

Service_Distinct Date Count =

CALCULATE(

DISTINCTCOUNT('agg_route_trip'[Date]),

ALLEXCEPT('agg_route_trip', 'agg_route_trip'[service])

)

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

Service Average EPK =

CALCULATE(

AVERAGE('agg_route_trip'[epk]),

ALLEXCEPT('agg_route_trip', 'agg_route_trip'[Service])

)

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

I created 2 new tables SummaryTableX (Service wise Avg EPK) & SummaryTableY (Service wise count of distinct dates). I connected both these tables to agg_route_trip table(main table) via one to many relationship through service column.

Now in the scatter plot there are 16 grids(4*4). Client wants to check each grid wise data.

For this I created a new column Grid3 based on service wise average EPK & count of distinct dates service wise. For the Grid3 column I used the below DAX

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

Grid3 =

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

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

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

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

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

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

VAR X_100th_Percentile = MAX(SummaryTableX[EPK])

VAR Y_100th_Percentile = MAX(SummaryTableY[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()

)

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

Client Requirement is On selection of particular grid, in the scatter plot should show only that grid data. This is working fine on the entire data. But if month filter is applied & in that particular month 16 grids are coming in the visual. Now Client wants to check month wise each grid data. Ex In month of july they want to check each grid wise. But I am facing issue here. Please help me. I hope I am clear with my explanation.

Below is the sample file

https://drive.google.com/file/d/1LUYxFUm_YNbO57f2Xmad2HZSyt1cz6NL/view?usp=sharing