cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cdawidow
Helper III
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.  

 

VTAB.PNG

 

2 ACCEPTED SOLUTIONS
Fowmy
Super User
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]
)

Fowmy_0-1623411547086.png

 



 

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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

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

 

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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

3 REPLIES 3
Fowmy
Super User
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]
)

Fowmy_0-1623411547086.png

 



 

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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

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

 

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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!