topic Re: Controlling filters in percentile calculation in DAX Commands and Tips

Controlling filters in percentile calculation

Kaii — Sat, 15 Jun 2024 00:29:59 GMT

I have this measure, I need the percentile lines to stay constant even if we select another "subject", so we can see if a subject falls above/below certain constant percentile values.

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

I am not sure which part I am doing wrong, this measure doesnt work properly with the "Type" filter which within the same table

as you can see when " Bird" Been selected. the precentile measure return as "Blank" .
This is how my table looks like

Could anyone help me figure out why?
Thank you very much

Re: Controlling filters in percentile calculation

Dangar332 — Sat, 15 Jun 2024 07:11:18 GMT

Hi, Kaii

I don't know i fully getting you or not

But try to add
Filter(All(Table[type]),true()) TO your calculate part

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

Best Regards,
Dangar

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

Re: Controlling filters in percentile calculation

Kaii — Sat, 15 Jun 2024 23:04:06 GMT

Hey, the measure doesn't work😥
Whta im look for is ignore the "subject" filter but still filter on "Type".

Thanks

Re: Controlling filters in percentile calculation

Dangar332 — Sun, 16 Jun 2024 05:20:38 GMT

Hi, Kaii

Measure =

VAR _ATABLE =

SUMMARIZE(

ALL('Table'[Company]),

'Table'[Company],

"VALUE", SUM('Table'[VALUE])

)

RETURN

CALCULATE(

PERCENTILEX.INC(_ATABLE, [VALUE], 0.5),

Filter(All(Table[Subject]),true())

)

Re: Controlling filters in percentile calculation

Kaii — Sun, 16 Jun 2024 06:30:12 GMT

Hey, thanks for the reply. But it still not working properly,
when I select "Bird" the measure should return as "4" but it returns as blank.

Re: Controlling filters in percentile calculation

Anonymous — Mon, 17 Jun 2024 02:59:34 GMT

Hi Dangar332 ,thanks for your quick reply, I will add more.

Hi Kaii ,

The Table data is shown below:

Use the following DAX expression to create a measure

Measure = CALCULATE(PERCENTILEX.INC('Table',[VALUE],0.5),ALL('Table'[subject]))

_Value = SUM('Table'[VALUE])

Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Controlling filters in percentile calculation

Kaii — Mon, 17 Jun 2024 19:37:59 GMT

Hey , Thanks for the reply,
Your measure works almost perfectly.....
it works very well when one Type has been selected, but if you didn't select any type. the numbers are not right.
For example,

I did not select anything, the 50%th line should return as (20+23)/2 =21.5 but it returns as 15 which is the the 50%th number without aggregation.

Do you know any way we can fix this?
Thank you so Much

Re: Controlling filters in percentile calculation

Anonymous — Tue, 18 Jun 2024 02:28:25 GMT

Hi Kaii ,

The Table data is shown below:

Please follow these steps:

1.Use the following DAX expression to create a table

Table 2 = SUMMARIZE('Table',[subject],"VALUE",SUM('Table'[VALUE]))

2.Use the following DAX expression to create a measure

Measure = IF(NOT ISFILTERED('Table'[Type]), PERCENTILEX.INC('Table 2',[VALUE],0.5), CALCULATE(PERCENTILEX.INC('Table',[VALUE],0.5),ALL('Table'[subject])))

3.Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.