Forum Discussion
Percentile as a measure
Microsoft EmployeeHi 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
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