PERCENTILEX.INC used in an IF statement (Not Working)

[Auktavius]

Greetings,

I have created two measures.

The first is a quartile measure using the percentilex.inc function:

             1st Quartile = PERCENTILEX.INC('Data Table','Data Table'[Total],.25)
This references a calculated column called "Total", which adds the value of 13 different columns to arrive at a total value.

              The 1st Quartile value for this data set is 22

The second is a total score measure using the sumx function:

             Total Score = SUMX('Data Table','Data Table'[ACS-S]+'Data Table'[ACW-S]+'Data Table'[Adh-S]+'Data Table'[AHT-S]+'Data Table'[Comp-S]+'Data Table'[CPH-S]+'Data Table'[CSAT-S]+'Data Table'[EPH-S]+'Data Table'[FCR E-S]+'Data Table'[FCR V-S]+'Data Table'[QA Score-S]+'Data Table'[WQPH-S])

This adds the same columns as the calculated column above. I created it twice to avoid circular reference in the following IF statement:

              Quartile Rank = IF('Data Table'[Total Score]<'Data Table'[1st Quartile],1,0)

When I run this function the result is 0 for all rows of data, even though some of the results are TRUE., i.e. < 22

When I change the statement to include an = sign, all results are 1, even though some of the results are FALSE, i.e. > 22
                Quartile Rank = IF('Data Table'[Total Score]<='Data Table'[1st Quartile],1,0)

I would greatly appreciate any help! 

Thank you in advance,

Peter

[truptis · Accepted solution]

@Auktavius , We are creating 2 tables.

table1 will have all the and  "_Rank" column with the values present in "LPRank"
table2 will have an additional column called "_Rank1" with the ranking of first table based upon "_Rank" column

[Auktavius · Accepted solution]

@truptis Worked like a charm! Thanks so much!

[truptis · Accepted solution]

@Auktavius , We are creating 2 tables.

table1 will have all the and  "_Rank" column with the values present in "LPRank"
table2 will have an additional column called "_Rank1" with the ranking of first table based upon "_Rank" column

[truptis]

Hi @Auktavius ,

Please see this solution:
https://community.powerbi.com/t5/Desktop/PERCENTILEX-INC-NOT-WORKING/m-p/721044#M347959 

[Auktavius]

Hello @truptis ,

Thank you for the response. I am still relatively new to PowerBI, and I really appreciatre your reply. Could you explainwhats going on in these first two statements: