Controlling filters in percentile calculation

[Kaii]

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 

[v-zhouwen-msft]

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.

[Kaii]

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

[v-zhouwen-msft · Accepted solution]

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.

[Dangar332]

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.

[Kaii]

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

Thanks 

[Dangar332]

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())

)

[Kaii]

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.