Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Yikes! Gosh I'm so new to this.
I have a table with the following:
Column A | Column B | Column C
Column A containts unique ID numbers associated to a member
Columb B contains the most recent year in which we appraised their performance [2018]
Column C contains the performance score
I'm trying to create two new measures:
A precentile column measure and a rank column measure. Any help on how to write this dax? I've looked around the forum and read the microsoft doc, but gettting stuck with the DAX language.
Here's what the table looks like atm.
Thanks,
Hi @Anonymous
Is this problem sloved?
If it is sloved, could you kindly accept it as a solution to close this case?
If not, please let me know.
Best Regards
Maggie
Hi @Anonymous
Create two measures
precentile = SUM('Table'[score])/CALCULATE(SUM('Table'[score]),ALL('Table')) rank = RANKX(ALL('Table'),CALCULATE(SUM('Table'[score])),,DESC,Dense)
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Maggie, for the most part this works. But when I use slicers to filter by the product category [text] the calculation doesn't adjust within that category.
Is it possible to modify the dax to calculate based on the product category? [for E.G. when i filter to apples, it carries over the calculation of the whole column of performance, I'd like it to adjust the calculation by category as well).
Thank you btw. The overall percentile and rank dax code works, if we can only get it to adjust the calculations based on the filtered category, we'll be golden!!
Hi @Anonymous ,
To get Rank Measure:
use this formula:
Rank = RANKX(ALLSELECTED('Table (3)'),CALCULATE(SUM('Table (3)'[COl C]),ALLEXCEPT('Table (3)','Table (3)'[COl A])),,DESC,Dense)
To get the percentile use this measure
Percentile = ( COUNTAX ( ALLSELECTED ( 'Table (3)' ), 'Table (3)'[COL A]) - [Rank] ) / ( COUNTAX ( ALLSELECTED( 'Table (3)'), 'Table (3)'[COL A] ) - 1 )
Please in the above formulas use your Columns names and Table Names.
Let me know if this works for you.
Thanks,
Tejaswi
@Anonymous - It appears that it returned for both the rank measure and precentile measure it returned 1. Another thing to note, when looking at the data in a table format, it's repeating for some reason . . ..
Hi @Anonymous ,
Would it be possible to provide your sample data in a excel spreadsheet?
Save your sample file in a dropbox and share a link of that file.
I will have to see whats wrong in the formula and why the data is repeating .
Thanks,
Tejaswi
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |