percentile calculating incorrectly

Hi Mi_80 ,

The presence of outliers and applied filters can significantly impact the results of the PERCENTILEX.INC calculation in DAX, especially if your dataset contains extreme values or a large number of zeros. These factors can skew percentile calculations, resulting in inaccurate results. Additionally, the use of ALLSELECTED in your measure ensures that slicer and filter context is respected, which might further alter the dataset used for percentile calculations if filters are applied incorrectly or interact in unexpected ways.

To address these issues, start by examining the intermediate summarized data. Using SUMMARIZE and CALCULATETABLE, you can review the total amounts grouped by Group to ensure they are calculated and filtered as intended:

SUMMARIZED_TABLE = 
CALCULATETABLE(
    SUMMARIZE(
        table,
        table[Group],
        "total", SUM(table[Amount])
    ),
    Filter(
        ALLSELECTED(table),
        table[status] = "Active" && 
        table[Region] = SELECTEDVALUE(table[Region])
    )
)

This intermediate table helps verify that the grouped totals align with your expectations. If outliers are present, you can dynamically filter them from the dataset. For example, exclude totals outside a statistically derived range:

FILTERED_TABLE = 
FILTER(
    SUMMARIZED_TABLE,
    [total] > 0 && [total] < SOME_THRESHOLD
)

SOME_THRESHOLD can be dynamically defined based on your data, such as using averages or standard deviations. Once the data is verified and refined, calculate the percentile while ensuring the [total] column is consistently evaluated:

PERCENTILE = 
PERCENTILEX.INC(
    FILTERED_TABLE,
    [total],
    0.25
)

To further troubleshoot, test the calculation without slicers or filters to confirm baseline accuracy:

TEST_PERCENTILE = 
PERCENTILEX.INC(
    ADDCOLUMNS(
        SUMMARIZE(table, table[Group]),
        "total", SUM(table[Amount])
    ),
    [total],
    0.25
)

This approach isolates the effects of slicers and filters and helps identify discrepancies. If inaccuracies persist, visualizing the data distribution with a histogram or boxplot can provide additional insights into the impact of outliers. These steps will help refine your measure and improve the accuracy of the percentile calculations.

 

Best regards,