Dynamic Percentile lines in scatter plot visual not working with month slicer

[Anonymous]

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 

[freginier]

The core reason the month slicer has no effect is that Grid3 is a calculated column. A calculated column is evaluated once, at data refresh, in a fixed (no-slicer) context - it physically stores one value per row and can't react to a slicer at report time. So whatever the month slicer does, every row keeps the Grid it was assigned at refresh.

On top of that, your feeder measures use ALLEXCEPT('agg_route_trip','agg_route_trip'[service]), which removes the filter from every column except service - including the month column - so even the measure logic is ignoring the month context.

To make it react to the month slicer:

1) Make the percentile thresholds (and the reference lines) measures, so they recalculate under the current month filter, e.g. wrap your PERCENTILEX.INC over SummaryTableX/Y in measures rather than baking them into a column.

2) Fix the feeder measures so they don't ALLEXCEPT the month away - keep the month/date in context (use REMOVEFILTERS only on the columns you actually need to clear, not the whole table).

One honest caveat: a scatter plot needs a column for the point grouping/Details, and a column can't be slicer-aware. So if you specifically need each point re-assigned to a different grid when the month changes, build SummaryTableX/Y at service + month grain (so "service in July" is its own row) and slice on that. That gives you month-reactive points plus measure-based percentile lines - which is something a single calculated column can't do by design.