Forum Discussion

PBI_Michael's avatar
PBI_Michael
Frequent Visitor
8 years ago

Percentile as a measure

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

 

 

 

 

Need Help

4 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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's avatar
      PBI_Michael
      Frequent Visitor
      8 years ago

      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's avatar
        v-yulgu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        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

  • vazfelipe's avatar
    vazfelipe
    Frequent Visitor
    8 years ago

    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.