Forum Discussion

deepuaits's avatar
deepuaits
Frequent Visitor
1 year ago
Solved

How to align Percentile, Median, and Average values between Scatter Plot and Table visual in Power B

I'm working with a Power BI scatter plot visual where:

  • X-axis = Response Count (a measure)

  • Y-axis = Value (Optionstring) it is string column

  • Legend = School Name

  • The visual is filtered by Cohort (e.g., via slicer)

I’m using the Analytics pane to show Min, 25th Percentile (Q1), Average, Median, 75th Percentile (Q3), and Max lines dynamically.

The dataset includes multiple years, and some schools appear multiple times (duplicates across years). I am calculating stats like Q1, Median, and Avg based on distinct school names, using either measures or the analytics pane in the scatter plot.

 

The issue:

  • In the scatter plot, the analytics lines show correct values based on distinct schools.

  • However, when I try to show the same statistical values in a Table visual, the values don’t match the scatter plot — especially Avg, Median, Q1, Q3.

What I need:

I want to replicate the same calculated values (Min, Q1, Median, Avg, Q3, Max) from the scatter plot into a Table visual, ensuring consistency — especially while dealing with duplicated school names (due to multiple years).

📌The business wants to see the data both ways — in a scatter plot and in a table visual — and expects the numbers to match.

 

How can I calculate these stats in a way that respects filters (like cohort) and avoids counting the same school multiple times in the calculations?

 

Thanks,

Deepu

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Ilgar_Zarbali's avatar
    Ilgar_Zarbali
    1 year ago

    This issue is a common one when comparing analytics pane values in a Scatter plot vs table visual calculations in Power BI, especially with duplicate entities like schools across years.

    Goal is - 

    Calculate: Min, Q1, Median, Avg, Q3, Max
    — based on distinct schools, filtered by Cohort, and used consistently in both Scatter and Table visuals.

     

    1: Create a summarized table of distinct schools with their X-axis measure

    SchoolSummaryTable =
SUMMARIZE(
    FILTER(
        'YourData',
        NOT ISBLANK([Response Count])  // Optional safeguard
    ),
    'YourData'[School Name],
    "ResponseCount", [Response Count]  // This is your X-axis measure
)

     

    You may also include filter context like Cohort, depending on your model.

     

    2. Use PERCENTILEX.INC and MEDIANX for stat measures:

     

    Avg_ResponseCount =
AVERAGEX(
    SchoolSummaryTable,
    [Response Count]
)

Min_ResponseCount =
MINX(
    SchoolSummaryTable,
    [Response Count]
)

Max_ResponseCount =
MAXX(
    SchoolSummaryTable,
    [Response Count]
)

Median_ResponseCount =
MEDIANX(
    SchoolSummaryTable,
    [Response Count]
)

Q1_ResponseCount =
PERCENTILEX.INC(
    SchoolSummaryTable,
    [Response Count],
    0.25
)

Q3_ResponseCount =
PERCENTILEX.INC(
    SchoolSummaryTable,
    [Response Count],
    0.75
)

    3. Make SchoolSummaryTable dynamic using ADDCOLUMNS with FILTER context
    If needed, turn it into a variable inside a measure to respect slicers like Cohort:

     

    Median_ResponseCount =
VAR SummaryTable =
    ADDCOLUMNS(
        SUMMARIZE('YourData', 'YourData'[School Name]),
        "ResponseCount", [Response Count]
    )
RETURN
    MEDIANX(SummaryTable, [Response Count])

    Now Use These Measures in Your Table Visual

     

    • Create a table visual (no need for rows — you can put all 6 stat measures side by side).
    • Ensure filters like Cohort are applied as slicers — they will propagate to the DAX via the [Response Count] measure.

     

    If you found the above information helpful, I’d appreciate it if you could give us a Kudos and mark the response as the Accepted Solution.

1 Reply

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

    This issue is a common one when comparing analytics pane values in a Scatter plot vs table visual calculations in Power BI, especially with duplicate entities like schools across years.

    Goal is - 

    Calculate: Min, Q1, Median, Avg, Q3, Max
    — based on distinct schools, filtered by Cohort, and used consistently in both Scatter and Table visuals.

     

    1: Create a summarized table of distinct schools with their X-axis measure

    SchoolSummaryTable =
SUMMARIZE(
    FILTER(
        'YourData',
        NOT ISBLANK([Response Count])  // Optional safeguard
    ),
    'YourData'[School Name],
    "ResponseCount", [Response Count]  // This is your X-axis measure
)

     

    You may also include filter context like Cohort, depending on your model.

     

    2. Use PERCENTILEX.INC and MEDIANX for stat measures:

     

    Avg_ResponseCount =
AVERAGEX(
    SchoolSummaryTable,
    [Response Count]
)

Min_ResponseCount =
MINX(
    SchoolSummaryTable,
    [Response Count]
)

Max_ResponseCount =
MAXX(
    SchoolSummaryTable,
    [Response Count]
)

Median_ResponseCount =
MEDIANX(
    SchoolSummaryTable,
    [Response Count]
)

Q1_ResponseCount =
PERCENTILEX.INC(
    SchoolSummaryTable,
    [Response Count],
    0.25
)

Q3_ResponseCount =
PERCENTILEX.INC(
    SchoolSummaryTable,
    [Response Count],
    0.75
)

    3. Make SchoolSummaryTable dynamic using ADDCOLUMNS with FILTER context
    If needed, turn it into a variable inside a measure to respect slicers like Cohort:

     

    Median_ResponseCount =
VAR SummaryTable =
    ADDCOLUMNS(
        SUMMARIZE('YourData', 'YourData'[School Name]),
        "ResponseCount", [Response Count]
    )
RETURN
    MEDIANX(SummaryTable, [Response Count])

    Now Use These Measures in Your Table Visual

     

    • Create a table visual (no need for rows — you can put all 6 stat measures side by side).
    • Ensure filters like Cohort are applied as slicers — they will propagate to the DAX via the [Response Count] measure.

     

    If you found the above information helpful, I’d appreciate it if you could give us a Kudos and mark the response as the Accepted Solution.