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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
larbpup
Frequent Visitor

Summarize table before calculating Percentile

I have a table of received POs that consist of a Supplier, date of PO, Qty that was received On Time, and Qty Recieved Overall. 

 

SupplierDateOnTimeQtyOverallQty
A2/2/2023410
A4/12/20233250
B8/1/202389120
C9/5/20236499

 

We would ultimately like use this as a supplier "score card" to show how Vendor A, for example, performed relative to all the other suppliers. As a further wrinkle, I would like to be able to filter by date range so we can look at performance MTD, YTD, Last YTD, etc. 

SupplierOn Time %Percentile
A60%0%
B74%66%
C64%33%

 

I am rusty on my stats, but if I recall Percentile = (# of rows below value) / (total # of rows) * 100  (feel free to correct me if mistaken), and I am also having trouble because I need to do an intermediate step where I calculate the On Time % for each Vendor and cannot do this in a calc column or table because it would not allow me to filter by date.

I understand that I need to do a Summarize or Values or some function to evaluate the On Time % first and then calculate percentile, but I do not think I fully understand how Summarize works (if that is the solution).

 

Can someone point me in the right direction re: using a temporary table within the measure?

My initial On TIme % measure is simple: 

 

OnTime% = 

var qty_ontime = sumx(VendorPerf,OnTimeQty)
var qty_overall = sumx(VendorPerf,OverallQty)

return divide(qty_ontime,qty_overall)

 

 

My percentile formula (via a youtube video: https://www.youtube.com/watch?v=PGCbSWeUe5c)

 

 

Percentile = 

var __ot = [OnTime%]

return
 if(
     HasOneValue(VendorPerf[Vendor]),
     Divide(
         Calculate(
            Countrows(VendorPerf),
            VendorPerf[OnTime%] < __ot
         ),
         Countrows(VendorPerf)
     )
     ,0
   )

 

 


What I am attemtping to use for summarize but not working, returns an error about "Parameter is not correct type" or "Cannot find colum".

 

Percentile = 

var vendors_ot = summarize(VendorPerf,VendorPerf[Vendor],"OT%",[OnTime%])

var __ot = [OnTIme%]

return
 if(
     HasOneValue(vendors_ot[Vendor]),
     Divide(
         Calculate(
            Countrows(vendors_ot),
            vendors_ot[OT%] < __ot
         ),
         Countrows(vendors_ot)
     )
     ,0
   )

 

1 REPLY 1
lbendlin
Super User
Super User

Have you considered using the built-in percentile functions in DAX?

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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