Forum Discussion
Power BI Dax Formula
I want to use Rank function so basically I want to rank on basis of Amout the amount which is highest it should be rank 1 but I also want monthwise means if in jan 2024 - 400 - Rank 1, 300- rank 2,,,, but if feb 2024 starts then 500 - should be rank 1 and 340- Rank 2 like this so this how i want rankung please help me
23 Replies
Hi Prashant_123, Please try to create below measure:
Monthly Rank =
RANKX(
FILTER(
ALL(YourTable),
YourTable[Month] = MAX(YourTable[Month])
),
YourTable[Amount],
,
DESC,
DENSE
)Hi Anmol
getting this error how to resolveToo many arguments were passed to the FILTER function. The maximum argument count for the function is 2.
Rank_Sales =RANKX(FILTER(ALL('CH - Sales Order By Customer','CH - Sales Order By Customer'[Month] = MAX('CH - Sales Order By Customer'[Month])),'CH - Sales Order By Customer'[Amount],,DESC,DENSE))
Create a Rank Measure
Rank By Amount =
VAR CurrentMonth = SELECTEDVALUE('Table'[Month])
RETURN
RANKX(
FILTER(
ALL('Table'),
'Table'[Month] = CurrentMonth
),
'Table'[Amount],
,
DESC,
DENSE
)Add Month and Amount to your visual.
Include the Rank By Amount measure in your table or matrix visual to show the rank for each row.💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedInHi Kedar_Pande
Rank_Sales =VAR CurrentMonth = SELECTEDVALUE('CH - Sales Order By Customer'[Month])RETURNRANKX(FILTER(ALL('CH - Sales Order By Customer'),'CH - Sales Order By Customer'[Month] = CurrentMonth),'CH - Sales Order By Customer'[Amount],,DESC,DENSE)
getting this error and this is the formula
A single value for column 'Amount' in table 'CH - Sales Order By Customer' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, counCan you try:
Rank_Sales =VAR CurrentMonth = SELECTEDVALUE('CH - Sales Order By Customer'[Month])RETURNRANKX(FILTER(ALL('CH - Sales Order By Customer'),'CH - Sales Order By Customer'[Month] = CurrentMonth),SUM('CH - Sales Order By Customer'[Amount]),,DESC,DENSE)
HI Prashant_123 ,
Please try the bellow DAX:
Rank Measure = RANKX( ALL(financials), CALCULATE( [Sales Amount], ALLEXCEPT(financials, financials[Month-Year]) ), , DESC, DENSE )Failed to resolve name 'financials'. It is not a valid table, variable, or function name.
What is this please be tell me ehat is the optionHi Prashant_123 ,
Replace the table and columns names with your owns.
This financials is not valid for you, because it is in my source data.
pls try this
Rn = VAR _max = CALCULATETABLE('Calendar',REMOVEFILTERS('Calendar'),VALUES('Calendar'[Year Month])) RETURN RANK(DENSE,_max,ORDERBY([Sales], DESC)) -------------------------------------------- or Rn = VAR _max = MAX('Calendar'[Year Month]) VAR _tbl = FILTER(ALL('Calendar'),'Calendar'[Year Month]=_max) RETURN RANKX(_tbl,[Sales],,DESC,Dense)Hi Ahmedx what is calendar here Cannot find table 'Calendar'., can you please check
- Hi AhmedxThis is also not working
Rank Measure =VAR _tbl= ALLSELECTED('CH - Sales Order By Customer'[Customer]) RETURN RANKX(_tbl,[Key],,DESC,Dense)
Can you please let me knowI need a file, I can't do without a file
HI Ahmedx
Can you please drop your email id so that I can share the Powerbi file and then it will be corrected as here I am not able to find the option to share file.
Please do asap
Thanks
Hi Prashant_123 ,
Please refer to the following link to upload your file to the community. Kindly ensure that you grant sufficient privileges to access your shared file. Thank you.
How to upload PBI in Community
Best Regards
Hi Ahmedx
Thanks for support now its working
Can you also please help in power automate ?
Do let me know
Hi Prashant_123 ,
It seems Ahmedx has provided you the solution. Is there anything else need help about this thread? What do you want to achieve in Power Automate? Could you please explain more details on it? Thank you.
Best Regards
