Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Supplier | Date | OnTimeQty | OverallQty |
A | 2/2/2023 | 4 | 10 |
A | 4/12/2023 | 32 | 50 |
B | 8/1/2023 | 89 | 120 |
C | 9/5/2023 | 64 | 99 |
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.
Supplier | On Time % | Percentile |
A | 60% | 0% |
B | 74% | 66% |
C | 64% | 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
)
Have you considered using the built-in percentile functions in DAX?
User | Count |
---|---|
25 | |
11 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
7 |