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.
I have a table visual and it has multiple columns present in it. I am trying to get the TOPN based on a particular column, even after there are no ties, it shows more than the required number. I have also used a RANKX function DAX but it has been returning 1 always.
Measure
RANKING = RANKX(ALL(Table Name), CALCULATE(SUM(Column Name(measure))). But it keeps throwing 1 itself.
Lets say I have numbers below
A | B | C | D | E | H |
ABC | Solid | 12345 | 0% | 123 | 1 |
DEF | Liquid | 12345 | 2% | 456 | 1 |
GHI | Gas | 23456 | 4% | 789 | 1 |
JKL | Solid | 45678 | 5% | 101 | 1 |
From the above example. Let me consider I want to calculate RANK for column E from the above table. But with the measure used I get Rank as 1 for all the rows i.e. Column H.
Can someone provide me with a better solution? It happens for TOPN too even if there are no ties.
Solved! Go to Solution.
Hi @sid-poly ,
The field [E] is a fact field in the table or a measure? It is a measure, could you please provide its formula? Please create a measure as below and check if you can get the correct rank values...
RANKING = RANKX ( ALLSELECTED ( 'Table' ), CALCULATE ( SUM ( 'Table'[E] ) ),,, DENSE )
In addition, you can refer the following threads to solve your problem.
Rank Product =
RANKX (
ALL ( MyData[Product] ),
CALCULATE ( SUM ( MyData[Quantity] ), ALLEXCEPT ( MyData, MyData[Product] ) )
)
Total Sales = SUM ( sales[sales amount] )
SalesRank = RANKX ( ALLSELECTED ( sales[employee number] ), [Total Sales] )
If the above one can't help you get the desired result, please provide some raw(fake) data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Try to add a column with the code below:
i tried and it works like this:
If I am doing it without the aggregation on the TableName[E] then it shows me the following error
A single value for column ColumnName in Table TableName cannot be determined and not allowing me to move forward. However, if I am applying the aggregation it still shows RANK as 1 for all of the rows.
the code is used to create a new column. Are you creating a measure?
Sorry was unable to reply due to max limits on private message.
Please find the screenshot below for the measure I have generated.
Hi @sid-poly ,
The field [E] is a fact field in the table or a measure? It is a measure, could you please provide its formula? Please create a measure as below and check if you can get the correct rank values...
RANKING = RANKX ( ALLSELECTED ( 'Table' ), CALCULATE ( SUM ( 'Table'[E] ) ),,, DENSE )
In addition, you can refer the following threads to solve your problem.
Rank Product =
RANKX (
ALL ( MyData[Product] ),
CALCULATE ( SUM ( MyData[Quantity] ), ALLEXCEPT ( MyData, MyData[Product] ) )
)
Total Sales = SUM ( sales[sales amount] )
SalesRank = RANKX ( ALLSELECTED ( sales[employee number] ), [Total Sales] )
If the above one can't help you get the desired result, please provide some raw(fake) data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
have to see the names for the table and column. Could replace them with dummy code?
I am not creating a measure, it is an exisiting column, but if I am not using Aggregation DAX like SUM it gives me the above mentioned error.
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 |
---|---|
41 | |
19 | |
19 | |
15 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |