topic VIRTUAL TABLE MEASURES in DAX Commands and Tips

VIRTUAL TABLE MEASURES

cdawidow — Fri, 11 Jun 2021 11:07:24 GMT

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.

Re: VIRTUAL TABLE MEASURES

Fowmy — Fri, 11 Jun 2021 11:39:16 GMT

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

Re: VIRTUAL TABLE MEASURES

cdawidow — Fri, 11 Jun 2021 12:27:28 GMT

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 ?

Re: VIRTUAL TABLE MEASURES

Fowmy — Fri, 11 Jun 2021 12:40:59 GMT

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