Percentile calc to remain constant on day but update on dimension filter use

[FOXYBARK]

HI there. 

Here is my table of data. 

Here is my proposed calc. I crafted this from another Fabric post. This calc works but does not provide my desired number. 

I am trying to get the 90th percentile for each floor. THis needs to remain constant across days. [PeopleCounts] are simply SUM(VALUE). 

Below is my ideal output. A constant line - 90th percentile line across days for each floor when I use a floor slicer. 

Thanks, F

[v-kpoloju-msft]

Hi @FOXYBARK,
Thank you for reaching out to the Microsoft fabric community forum.

After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it.    

Dax Measure for Creating 90^th Percentile:

90th Percentile =

IFERROR(

    PERCENTILEX.INC(

        CALCULATETABLE(

            'August Badge Data',

            REMOVEFILTERS('August Badge Data'[Date])

        ),

        'August Badge Data'[Value],

        0.9

    ),

0)

I am also including .pbix file for your better understanding, please have a look into it:

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[FOXYBARK]

Hi there @v-kpoloju-msft Thank you for this. There is one added layer to this and I made the small modification in your sample workbook. I'd like the values summed by day and then the percentile calculated off the summed values. For example, now the 5th floor, 90th percentile is 31.4. Correction: I cannot attach the .pbi file so here are some screenshots of what I did. How would you modify your calculation to account for this please?

[v-kpoloju-msft]

Hi @FOXYBARK,
Thank you for the update and clarification.

You are correct, To calculate the 90th percentile, we should use the daily summed values per Date instead of the raw data.

To do this, I suggest adjusting the DAX to first summarize the Value by day and then apply PERCENTILEX.INC over those results. Here is a measure that should meet your requirements:

90th Percentile Daily Total =

VAR FloorFilter = SELECTEDVALUE('YourTable'[Floor])

VAR DailySums =

    ADDCOLUMNS(

        SUMMARIZE(

            'YourTable',

            'YourTable'[Date]

        ),

        "DailyTotal",

        CALCULATE(

            SUM('YourTable'[Value]),

            'YourTable'[Floor] = FloorFilter

        )

    )

RETURN

    PERCENTILEX.INC(DailySums, [DailyTotal], 0.9)

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[FOXYBARK]

Beautiful. And how to keep the percentile constant across all dates? @v-kpoloju-msft 

[v-kpoloju-msft]

Hi @FOXYBARK,

Thank you for the kind words, I’m glad the measure worked for you.

To keep the 90th percentile constant across all dates, you’ll need to remove the current date context from the calculation so that the percentile is calculated once over the entire dataset (filtered by Floor, if applicable), and then the same value is shown regardless of the date in the visual. You can update your measure like this:

90th Percentile Daily Total (Constant) =

VAR FloorFilter = SELECTEDVALUE('YourTable'[Floor])

VAR DailySums =

    ADDCOLUMNS(

        SUMMARIZE(

            'YourTable',

            'YourTable'[Date]

        ),

        "DailyTotal",

        CALCULATE(

            SUM('YourTable'[Value]),

            'YourTable'[Floor] = FloorFilter,

            REMOVEFILTERS('YourTable'[Date])

        )

    )

RETURN

    PERCENTILEX.INC(DailySums, [DailyTotal], 0.9)


The key change is the use of REMOVEFILTERS('YourTable'[Date]) inside the CALCULATE function, which ensures that the date context does not affect the percentile calculation making it constant across the visual.

If this helps, please consider giving a "Kudos" or marking it as Accepted Solution to assist others in the community.

Thanks again for using the Microsoft Community Forum!

[v-kpoloju-msft]

Hi @FOXYBARK,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[FOXYBARK]

Hi. 

This did not work as the adding the REMOVEFILTER function provided a constant of all summed values for the floor, it did not provide a 90th percentile value. 

[FOXYBARK · Accepted solution]

I will reply to my own post with the measure that worked. This calc did exactly what I needed it to do.

Measure = PERCENTILEX.INC(

    ALLSELECTED('August Badge Data'[Date]),

    CALCULATE(

        SUM('August Badge Data'[Value])),.9)

Thanks

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...