Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi guys, I am trying to calculate the sales of my customers who are greater than the 95th percentile of all customers within each segment. The total is correct, but I want the measure to calculate the unique/distinct customers who attribute towards that top percentile sales volume. As you can see, each customer has a blank value despite the total being accurate. Any ideas of how I can tweak my measures?
Essentially I want the measure to calculate only the customers sales figures from Hill Supplies to Mr. Rooter.
Solved! Go to Solution.
@cdawidow
You can follow the measure given below, I did it with my sample data and you can adopt it.
Cutomers 95 Pcntl =
var __p = PERCENTILEX.INC( ALLSELECTED('Customer Table'[customore name]),[Total Sales], .95 ) return
SUMX(
FILTER(
VALUES('Customer Table'[customore name]),
[Total Sales] >= __p
),
[Total Sales]
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@cdawidow
Please try this:
Cutomers 95 Pcntl =
var __cust = FILTER( ALLSELECTED('Customer Table'[customore name]), [Total Sales] > 0 )
var __p = PERCENTILEX.INC( __cust,[Total Sales], .95 ) return
SUMX(
FILTER(
VALUES('Customer Table'[customore name]),
[Total Sales] >= __p
),
[Total Sales]
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@cdawidow
You can follow the measure given below, I did it with my sample data and you can adopt it.
Cutomers 95 Pcntl =
var __p = PERCENTILEX.INC( ALLSELECTED('Customer Table'[customore name]),[Total Sales], .95 ) return
SUMX(
FILTER(
VALUES('Customer Table'[customore name]),
[Total Sales] >= __p
),
[Total Sales]
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thank you ! This works perfectly! I was wondering, I want to exclude all negative sale values so anything over $0 would be excluded. How can I add the filter to the percentile calculation ?
@cdawidow
Please try this:
Cutomers 95 Pcntl =
var __cust = FILTER( ALLSELECTED('Customer Table'[customore name]), [Total Sales] > 0 )
var __p = PERCENTILEX.INC( __cust,[Total Sales], .95 ) return
SUMX(
FILTER(
VALUES('Customer Table'[customore name]),
[Total Sales] >= __p
),
[Total Sales]
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
User | Count |
---|---|
49 | |
48 | |
19 | |
16 | |
15 |
User | Count |
---|---|
110 | |
45 | |
42 | |
24 | |
20 |