Forum Discussion

cdawidow's avatar
cdawidow
Icon for Helper III rankHelper III
5 years ago
Solved

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.  

 

 

DAX
Help Requested
Virtual Table
  • Fowmy's avatar
    Fowmy
    5 years ago

    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's avatar
    Fowmy
    5 years ago

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

     

3 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

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

     



     

    • cdawidow's avatar
      cdawidow
      Icon for Helper III rankHelper III
      5 years ago

      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 ? 

      • Fowmy's avatar
        Fowmy
        Icon for Super User rankSuper User
        5 years ago

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