Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Get Fabric Certified for FREE during AI Skills Fest. This week only. Secure your voucher now.

Reply
Tommy_
Regular Visitor

Count duration of 50%, 90%, 99% records

‎07-16-2025 02:24 AM

Hi everyone, please help me, I can't find a solution 😞

What I need to do is count, how many records (filtered by Count=1 and Service=No), have duration grouped in groups of 50%, 90%. 99%.


For example: in May, there were 900 records (sorted by ASC duration), 450th record (50% of 900) have a duration of "01:20:00", so we count how many records have a shorter duration. Then, the 810th record (90% of 900) has a duration of "4:55:00", so we count the records that have a shorter duration. The same for 99%.


As a result, I would like to get a table like the one below:

 2025-012025-022025-03
 DurationRecordsDurationRecordsDurationRecords
50%01:5434501:5541201:56345
90%09:2454610:0167110:48445
99%24:07:0082024:45:0087732:57:00783

 

My source Table:

MonthDate StartDate FinishDurationCountService
2025-052025-05-06 20:59:002025-05-07 12:00:0015:01:001Yes
2025-052025-05-05 21:21:002025-05-06 11:40:0014:19:001Yes
2025-052025-05-03 21:03:002025-05-04 10:06:0013:03:001Yes
2025-052025-05-09 12:15:002025-05-09 17:40:005:25:001Yes
2025-052025-05-24 13:43:002025-05-24 14:36:000:53:001Yes
2025-052025-05-20 11:53:002025-05-20 13:15:001:22:001Yes
2025-052025-05-15 17:16:002025-05-15 18:20:001:04:000Yes
2025-052025-05-18 07:35:002025-05-18 10:34:002:59:001Yes
2025-052025-05-12 07:49:002025-05-12 08:52:001:03:001No
2025-052025-05-19 01:35:002025-05-19 09:20:007:45:001No
2025-052025-05-16 11:36:002025-05-16 13:17:001:41:000Yes
2025-052025-05-06 12:42:002025-05-06 16:10:003:28:001Yes
2025-052025-05-08 16:58:002025-05-08 17:25:000:27:001Yes
2025-052025-05-28 18:00:002025-05-28 20:14:002:14:001Yes
2025-052025-05-09 08:49:002025-05-09 10:00:001:11:000Yes
2025-052025-05-15 18:59:002025-05-15 20:00:001:01:000Yes
2025-052025-05-09 08:24:002025-05-09 14:23:005:59:001Yes
2025-052025-05-07 10:18:002025-05-07 12:56:002:38:001Yes
2025-052025-05-21 16:30:002025-05-21 18:55:002:25:001Yes
2025-052025-05-27 11:27:002025-05-27 13:30:002:03:001Yes
2025-052025-05-04 08:18:002025-05-04 10:20:002:02:001No
2025-052025-05-07 16:29:002025-05-07 17:50:001:21:001Yes
2025-052025-05-30 09:15:002025-05-30 10:30:001:15:001Yes
2025-052025-05-02 08:26:002025-05-02 09:35:001:09:001No
2025-052025-05-26 10:55:002025-05-26 12:00:001:05:001No

 

THX

Labels:
Message 1 of 5
500 Views
0
1 ACCEPTED SOLUTION
rohit1991
Super User
Super User

‎07-16-2025 04:32 AM

Hi @Tommy_ ,

You can achieve this in Power BI using DAX to calculate duration percentiles and count records below each threshold for each month, filtered by [Count]=1 and [Service]="No".

 

Step 1: Filtered Table: Create a calculated table or use DAX filters in your measure to work only with records where [Count]=1 and [Service]="No".


Step 2: Percentile Duration Measures: Create measures for each percentile (e.g., 50%, 90%, 99%):

Duration_50 =
PERCENTILEX.INC(
    FILTER('Table', 'Table'[Count]=1 && 'Table'[Service]="No"),
    'Table'[Duration],
    0.5
)

Duration_90 =
PERCENTILEX.INC(
    FILTER('Table', 'Table'[Count]=1 && 'Table'[Service]="No"),
    'Table'[Duration],
    0.9
)

Duration_99 =
PERCENTILEX.INC(
    FILTER('Table', 'Table'[Count]=1 && 'Table'[Service]="No"),
    'Table'[Duration],
    0.99
)

 
Step 3: Count Records Below Each Percentile:  Create measures to count records with duration less than or equal to each percentile value:

Records_50 =
CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        'Table',
        'Table'[Count]=1 &&
        'Table'[Service]="No" &&
        'Table'[Duration] <= [Duration_50]
    )
)

Records_90 =
CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        'Table',
        'Table'[Count]=1 &&
        'Table'[Service]="No" &&
        'Table'[Duration] <= [Duration_90]
    )
)

Records_99 =
CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        'Table',
        'Table'[Count]=1 &&
        'Table'[Service]="No" &&
        'Table'[Duration] <= [Duration_99]
    )
)

 

Step 4: Display in Table:  Build a matrix visual with Month as rows and your measures (Duration_50, Records_50, etc.) as values.


Did it work? ✔ Give a Kudo • Mark as Solution – help others too!

View solution in original post

Message 2 of 5
458 Views
4 REPLIES 4
v-sshirivolu
Community Support
Community Support

‎07-18-2025 12:21 AM

Hi @Tommy_ ,
Thank you for reaching out to Microsoft Fabric Community 

Try these steps

Add Duration in Minutes

Create a new column with - 

DurationMinutes =
HOUR([Duration]) * 60 +
MINUTE([Duration]) +
SECOND([Duration]) / 60

Create PercentileTable for Duration Thresholds
PercentileTable =
ADDCOLUMNS (
    SUMMARIZE (
        FILTER (
            'Table',
            'Table'[Count] = 1 && 'Table'[Service] = "No"
        ),
        'Table'[Month]
    ),
    "Duration50", PERCENTILEX.INC (
        FILTER (
            'Table',
            'Table'[Count] = 1 &&
            'Table'[Service] = "No" &&
            'Table'[Month] = EARLIER('Table'[Month])
        ),
        'Table'[DurationMinutes], 0.5
    ),
    "Duration90", PERCENTILEX.INC (
        FILTER (
            'Table',
            'Table'[Count] = 1 &&
            'Table'[Service] = "No" &&
            'Table'[Month] = EARLIER('Table'[Month])
        ),
        'Table'[DurationMinutes], 0.9
    ),
    "Duration99", PERCENTILEX.INC (
        FILTER (
            'Table',
            'Table'[Count] = 1 &&
            'Table'[Service] = "No" &&
            'Table'[Month] = EARLIER('Table'[Month])
        ),
        'Table'[DurationMinutes], 0.99
    )
)

Create Measures to Count Records Under Each Threshold

 

Add these measures to the main table:

CountBelow50 =
VAR CurrentMonth = MAX('Table'[Month])
VAR Threshold = CALCULATE(
    MAX('PercentileTable'[Duration50]),
    'PercentileTable'[Month] = CurrentMonth
)
RETURN
CALCULATE(
    COUNTROWS('Table'),
    'Table'[Count] = 1,
    'Table'[Service] = "No",
    'Table'[Month] = CurrentMonth,
    'Table'[DurationMinutes] <= Threshold
)

Repeat for CountBelow90 and CountBelow99 by referencing the corresponding percentile columns.

CountBelow90 = ... [use Duration90]
CountBelow99 = ... [use Duration99]

Create PercentileType  - New Table

PercentileType =
DATATABLE (
    "Percentile", STRING,
    {
        {"50%"},
        {"90%"},
        {"99%"}
    }
)

Create These Measures seperately for Matrix Display

In the PercentileType table, add:

DurationDisplayDurationDisplay =
SWITCH(
    SELECTEDVALUE(PercentileType[Percentile]),
    "50%", CALCULATE(MAX('PercentileTable'[Duration50])),
    "90%", CALCULATE(MAX('PercentileTable'[Duration90])),
    "99%", CALCULATE(MAX('PercentileTable'[Duration99]))
)

RecordCountDisplayRecordCountDisplay =
SWITCH(
    SELECTEDVALUE(PercentileType[Percentile]),
    "50%", [CountBelow50],
    "90%", [CountBelow90],
    "99%", [CountBelow99]
)

FormattedDuration in hh:mmFormattedDuration =
VAR Min = [DurationDisplay]
VAR H = INT(Min / 60)
VAR M = MOD(Min, 60)
RETURN
FORMAT(H, "00") & ":" & FORMAT(M, "00")

Add the Matrix Visual

Rows - PercentileType[Percentile]
Columns - Table[Month]
Values - FormattedDuration, RecordCountDisplay

Please find the attached .pbix file for your reference.

Regards,
Sreeteja.

records matrix.pbix
Message 5 of 5
416 Views
0
danextian
Super User
Super User

‎07-16-2025 05:03 AM

Hi @Tommy_ 

Given your sample data, what is your expected result? What if there were 5 records left after applying the filters, would be 50% be the 2nd or 3rd record?





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
Message 4 of 5
445 Views
0
rohit1991
Super User
Super User

‎07-16-2025 04:32 AM

Hi @Tommy_ ,

You can achieve this in Power BI using DAX to calculate duration percentiles and count records below each threshold for each month, filtered by [Count]=1 and [Service]="No".

 

Step 1: Filtered Table: Create a calculated table or use DAX filters in your measure to work only with records where [Count]=1 and [Service]="No".


Step 2: Percentile Duration Measures: Create measures for each percentile (e.g., 50%, 90%, 99%):

Duration_50 =
PERCENTILEX.INC(
    FILTER('Table', 'Table'[Count]=1 && 'Table'[Service]="No"),
    'Table'[Duration],
    0.5
)

Duration_90 =
PERCENTILEX.INC(
    FILTER('Table', 'Table'[Count]=1 && 'Table'[Service]="No"),
    'Table'[Duration],
    0.9
)

Duration_99 =
PERCENTILEX.INC(
    FILTER('Table', 'Table'[Count]=1 && 'Table'[Service]="No"),
    'Table'[Duration],
    0.99
)

 
Step 3: Count Records Below Each Percentile:  Create measures to count records with duration less than or equal to each percentile value:

Records_50 =
CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        'Table',
        'Table'[Count]=1 &&
        'Table'[Service]="No" &&
        'Table'[Duration] <= [Duration_50]
    )
)

Records_90 =
CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        'Table',
        'Table'[Count]=1 &&
        'Table'[Service]="No" &&
        'Table'[Duration] <= [Duration_90]
    )
)

Records_99 =
CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        'Table',
        'Table'[Count]=1 &&
        'Table'[Service]="No" &&
        'Table'[Duration] <= [Duration_99]
    )
)

 

Step 4: Display in Table:  Build a matrix visual with Month as rows and your measures (Duration_50, Records_50, etc.) as values.


Did it work? ✔ Give a Kudo • Mark as Solution – help others too!
Message 2 of 5
459 Views
Tommy_
Regular Visitor
In response to rohit1991

‎07-18-2025 02:09 AM

Thank you all, I used the rohit's solution:

Duration 50% = 
VAR Perc = PERCENTILEX.INC(
    FILTER('Table', 'Table'[Count]=1 && 'Table'[Service]="No"),
    'Table'[Duration_in_sec],
    0.5
)

VAR Dur = FORMAT(INT(
IF(MOD(Perc,60)=60,0,MOD(Perc,60)) + 
IF(MOD(INT(Perc/60),60)=60,0,MOD(INT(Perc/60),60)*100) +
INT(Perc/3600)*10000), "00:00:00")

VAR Rec = 
CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        'Table',
        'Table'[Count]=1 &&
        'Table'[Service]="No" &&
        'Table'[Duration_in_sec] <= Perc
    ))

Return Dur & " -> " & Rec

Tommy__1-1752829748903.png

 

 

THX

Message 3 of 5
387 Views
0

Helpful resources

Announcements
May Power BI Update Carousel

Power BI Monthly Update - May 2026

Check out the May 2026 Power BI update to learn about new features.

Fabric SQL PBI Data Days

Data Days 2026 coming soon!

Sign up to receive a private message when registration opens and key events begin.

New to Fabric survey Carousel

New to Fabric Survey

If you have recently started exploring Fabric, we'd love to hear how it's going. Your feedback can help with product improvements.

Power BI DataViz World Championships carousel

Power BI DataViz World Championships - June 2026

A new Power BI DataViz World Championship is coming this June! Don't miss out on submitting your entry.

View All