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 IV
Super User IV

@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!

Proud to be a Super User!

Website   YouTube    LinkedIn   Join my Power BI User

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!

Proud to be a Super User!

Website   YouTube    LinkedIn   Join my Power BI User

View solution in original post

3 REPLIES 3
Fowmy
Super User IV
Super User IV

@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!

Proud to be a Super User!

Website   YouTube    LinkedIn   Join my Power BI User

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!

Proud to be a Super User!

Website   YouTube    LinkedIn   Join my Power BI User

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors