topic Re: How to get the percentile calculation to ignore any filter from one column in DAX Commands and Tips

How to get the percentile calculation to ignore any filter from one column

Kaii — Thu, 13 Jun 2024 04:26:36 GMT

I have this measure, I need the percentile lines on visuals to stay constant even if we select another company. so that we can see if a client falls above/below certain constant percentile values when selected.

I am not sure which part I am doing wrong,

Measure = VAR _ATABLE=SUMMARIZE('Table','Company)'[Company],"VALLUE",SUM('Table'[VALUE]))

RETURN

CALCULATE(PERCENTILEX.INC(_ATABLE,[VALLUE],.5),ALL('Company)'[Company]))

even I use ALL('Company)'[Company]), the number still change after other company been selected.

Re: How to get the percentile calculation to ignore any filter from one column

Kaii — Thu, 13 Jun 2024 05:11:46 GMT

bump

Re: How to get the percentile calculation to ignore any filter from one column

VahidDM — Thu, 13 Jun 2024 06:09:23 GMT

Hi Kaii

Us ethis if you want to ignore all filters on the 'Table', ensuring that the percentile calculation is based on all data, regardless of any selections made on the 'Company' column.

Measure = 
VAR _ATABLE = 
    SUMMARIZE(
        ALL('Table'),
        'Table'[Company],
        "VALUE", SUM('Table'[VALUE])
    )
RETURN
CALCULATE(
    PERCENTILEX.INC(_ATABLE, [VALUE], 0.5)
)

If you want to ensure that only the 'Company' filter is ignored while other filters are respected, you can modify it like this:

Measure = 
VAR _ATABLE = 
    SUMMARIZE(
        ALL('Table'[Company]),
        'Table'[Company],
        "VALUE", SUM('Table'[VALUE])
    )
RETURN
CALCULATE(
    PERCENTILEX.INC(_ATABLE, [VALUE], 0.5)
)

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

LinkedIn|Twitter|Blog |YouTube

Re: How to get the percentile calculation to ignore any filter from one column

Kaii — Thu, 13 Jun 2024 16:35:41 GMT

Hey just one problem, when I select the other filter, those lines return as 0 not sure why