Percentile as a measure

[PBI_Michael]

Hi, I calculated the ranking of a data set using this DAX expression: 

Rank = if(not(ISBLANK([Avg Value1])), RANKX(ALLSELECTED('Table1'[ProductID]), [Avg Value1], , DESC, Dense), Blank())

which returns the ranking of all products by average Measure1, now my problem is that the ranking is a measure, not a column, I made it this way because I need the ranking to be dynamic and adapt as filters and slicers are applied to the report.

I saw a solution similar to my problem here: https://community.powerbi.com/t5/Desktop/PERCENTRANK-Inclusive/m-p/81208 

But that solution applies to columns as they use the countA function : 

So, my question is how can I get the same result using my measure and not creating columns? 

Thanks in advance

[vazfelipe]

Hi, 

Im reactivating this one trying to get related answers about PERCENTILE in DAX 2013 version. 

If I have a table with categories (A, B, C) and it has a column of values, how can I get a PERCENTILE 80% for each category as a measure, not column?

Please, I have tried a lot, cos there's no PERCENTILE.INC as a function in 2013 version.

[v-yulgu-msft]

Hi @PBI_Michael,

You can try below measures:

Rank =
IF (
    NOT ( ISBLANK ( [Avg Value1] ) ),
    RANKX ( ALLSELECTED ( Percentile[ProductID] ), [Avg Value1],, DESC, DENSE ),
    BLANK ()
)

PctRank =
 ( COUNTAX ( ALLSELECTED ( Percentile ), Percentile[ProductID] ) - [Rank] )
    / ( COUNTAX ( ALLSELECTED ( Percentile ), Percentile[ProductID] ) - 1 )

 

However, if your desired output is like this:

You need to relplace the ALLSELECTED function with ALL.

PctRank =
 ( COUNTAX ( ALL ( Percentile ), Percentile[ProductID] ) - [Rank] )
    / ( COUNTAX ( ALL ( Percentile ), Percentile[ProductID] ) - 1 )

Best regards,

Yuliana Gu

[PBI_Michael]

Hi Juliana Thank you very much, I'm a bit confused by the use of the word Percentile in your solution which seems to be a table name, let me put an example of how the data is organized in my tables:

I created the merged table because I needed to create a weighted average of the Value1 and Value2

so I used sumx to create a measure for the weighted average of value1 called: Avg Value1 in MergedProduct-DataTable

then used the RankX function as described to create a measure that returns a rank based on Avg Value1 

I think so far so good, now based on the way my data is organized, can you please re-write your formulas as I'm unsure what table names and columns should I use and can't understand the (Percentile[ProductID) and (Percentile) mean in this context.

Again thank you very much! 

[v-yulgu-msft]

Hi @PBI_Michael,

'Percentile' is the table name which you created measures based on. Percentile[ProductID] is the same as 'Table1'[ProductID] in the DAX formula you provided in your original post.

Regards,

Yuliana Gu