Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I have a table with the products quantity sales.
In the example I have product A that repeats 3 times and it is rank 1, product B repeats 2 times and it's rank 2 and finally product C repeats 1 one time, so it's rank 3.
The question it is: how to calculate the rank or row number?
Solved! Go to Solution.
Hi,
Write these measures:
Instances = countrows(Data)
Rank = RANKX(ALL(Data[Product]),[Instances])
Hope this helps.
Hi @rbustamante
First, create column / measure for the count of category:
Measure / Column = CALCULATE ( COUNTROWS ( 'Table' ) , ALLEXCEPT ( 'Table' , Table[Product] ) )
Rank = RANKX ( 'Table' , Table[Column] )
Measure =
VAR _1 = CALCULATE ( COUNTROWS ( 'Table' ) , ALLEXCEPT ( 'Table' , Table[Product] ) )
VAR _2 = RANKX ( 'Table' , 'Table'[Rank] )
RETURN
_2
RANKX ( 'Table' , 'Table'[Column] , , ASC , Dense )
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
Flexible solution,
Excel worksheet formula is enough
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hi @rbustamante
First, create column / measure for the count of category:
Measure / Column = CALCULATE ( COUNTROWS ( 'Table' ) , ALLEXCEPT ( 'Table' , Table[Product] ) )
Rank = RANKX ( 'Table' , Table[Column] )
Measure =
VAR _1 = CALCULATE ( COUNTROWS ( 'Table' ) , ALLEXCEPT ( 'Table' , Table[Product] ) )
VAR _2 = RANKX ( 'Table' , 'Table'[Rank] )
RETURN
_2
RANKX ( 'Table' , 'Table'[Column] , , ASC , Dense )
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
Hi,
Write these measures:
Instances = countrows(Data)
Rank = RANKX(ALL(Data[Product]),[Instances])
Hope this helps.
Hi @rbustamante
First, create column / measure for the count of category:
Measure / Column = CALCULATE ( COUNTROWS ( 'Table' ) , ALLEXCEPT ( 'Table' , Table[Product] ) )
Rank = RANKX ( 'Table' , Table[Column] )
Measure =
VAR _1 = CALCULATE ( COUNTROWS ( 'Table' ) , ALLEXCEPT ( 'Table' , Table[Product] ) )
VAR _2 = RANKX ( 'Table' , 'Table'[Rank] )
RETURN
_2
RANKX ( 'Table' , 'Table'[Column] , , ASC , Dense )
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
User | Count |
---|---|
97 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
96 | |
95 | |
64 | |
57 |