Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Kaii
Helper I
Helper I

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

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.

Kaii_0-1718252427812.png

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.

 

1 ACCEPTED SOLUTION
VahidDM
Super User
Super User

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

View solution in original post

3 REPLIES 3
VahidDM
Super User
Super User

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

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

Kaii
Helper I
Helper I

bump

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.