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 III

## VIRTUAL TABLE MEASURES

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.

2 ACCEPTED SOLUTIONS
Super User

@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]
)
``````

Did I answer your question? Mark my post as a solution! and hit thumbs up
Super User

@cdawidow

``````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]
)
``````

Did I answer your question? Mark my post as a solution! and hit thumbs up
3 REPLIES 3
Super User

@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]
)
``````

Did I answer your question? Mark my post as a solution! and hit thumbs up
Helper III

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 ?

Super User

@cdawidow

``````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]
)
``````

Did I answer your question? Mark my post as a solution! and hit thumbs up

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.