Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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")
Regards,
Charlie Liao
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")
Regards,
Charlie Liao
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |