cancel
Showing results 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

Helper II

## Percentile calculation that ignores outside filters

Hi

How do I edit this DAX code (used to calculate percentile in terms of [Client] experiencing [ID]) to ensure that the only filter it pays attention to is [Start Time]?

I plan to plot the output value by the Start Time column. I want the end values to stay constant even when you apply filters. eg filter on a single client, to show if that client's straight [ID] volume falls below or above the xth percentile of incident volume for that month.

Can you help? I have 2 approaches outlined below but ino matter what I try (ALLEXCEPT, ALL) I am unable to ignore filters (other than start time).

Sample data (there are lots more columns that I would want to filter on and have the output values stay constant , but for example):

 ID Client Start Time 1 a 02/01/2021 05:00 1 b 02/01/2021 05:00 1 c 02/01/2021 05:00 2 b 02/01/2021 00:00 2 c 02/01/2021 00:00 3 b 01/01/2021 00:00

Approach 1#

Testing Top .5% =
VAR allClients = VALUES('Incidents'[Client])
VAR SummaryTable =
VALUES('Incidents'[Client]),
"Distinct ID Count",
CALCULATE(
SUMX(
DISTINCT('Incidents'[Client]),
CALCULATE(DISTINCTCOUNT('Incidents'[ID])+0)
)
)
RETURN
CALCULATE(
PERCENTILEX.INC (
SummaryTable,
[Distinct ID Count],
0.995), ALLSELECTED('Incidents'[Client]))

Approach 2#

Top .5% =
VAR SummaryTable =
SUMMARIZE (
'Incidents',
'Incidents'[Client],
"Distinct ID Count", CALCULATE(SUMX(DISTINCT('Incidents'[Client]), CALCULATE(DISTINCTCOUNT('Incidents'[ID])+0)))
)

RETURN
CALCULATE(
PERCENTILEX.INC (
(SummaryTable),
[Distinct ID Count],
0.995
),
ALLSELECTED('Incidents'),
'Incidents'[Start Time] = MAX('Incidents'[Start Time]))
5 REPLIES 5
Helper I

Hey I am having the same problem.
Have you find any solution yet?

Thanks

Helper II

Unfortunately not - when I filter by client the output value actually just reverts to some kind of percentile calculation for all the [ID] that the client is on rather than keeping constant. Its like it limits the group being calculated for to only clients that show in [ID] that your selected client also shows up in. Any other ideas?

Unfiltered on client:

Filtered on client - the Top .5% value moves with the filter:

Responsive Resident

Dear ,

Can you share some sample of the PBIX File , it will be helpfull to test it out and see what is happenieng .

Regards ,

Responsive Resident

``````RETURN CALCULATE(
PERCENTILEX.INC (
SummaryTable,
[Distinct ID Count],
0.995),
ALLEXCEPT('Incidents', 'Incidents'[Start Time]))``````

see if this works with you ALLEXCEPT in approach 2

Helper I

I am having the same problem, but your measure doesnt really work....

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors