Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dinovic
Frequent Visitor

Filter Percentile expression to only evaluate on current month

I have a table Table1

Person        Date (Month)       Score       Status
Jane             2016-01-01            11            Good
Steven         2016-01-01             4              Bad
Richard       2016-01-01             5              Bad

Albert         2016-01-01             7              Bad

Jane           2016-02-01              8             Good
Steven       2016-02-01              5              Bad
Richard     2016-02-01              4              Bad

Albert       2016-02-01              8              Good

Status is derived by the following fomula :

IF(Table1[Score]>PERCENTILE.EXC(Table1[Score],0.8),"Good","Bad"))

This evaluates the score for the whole dataset, and returns a "Good" if the score is higher then the score of the 80th percentile.

My question is: How do I filter the formula above to only evaluate the score to the 80th percentile of the current month?

Thanks

1 ACCEPTED SOLUTION
v-caliao-msft
Employee
Employee

@dinovic,

 

Create a calcualted column to return Year and month.
YearMonth = FORMAT(Table1[Date],"YYYYMM")
Then you can use the expression below to achieve your requirement.
Column = IF(Table1[Score]>=CALCULATE(PERCENTILE.EXC(Table1[Score],0.8),ALLEXCEPT(Table1,Table1[YearMonth])),"Godd","Bad")
Capture.PNG

 

Regards,

Charlie Liao

View solution in original post

1 REPLY 1
v-caliao-msft
Employee
Employee

@dinovic,

 

Create a calcualted column to return Year and month.
YearMonth = FORMAT(Table1[Date],"YYYYMM")
Then you can use the expression below to achieve your requirement.
Column = IF(Table1[Score]>=CALCULATE(PERCENTILE.EXC(Table1[Score],0.8),ALLEXCEPT(Table1,Table1[YearMonth])),"Godd","Bad")
Capture.PNG

 

Regards,

Charlie Liao

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.