Power BI Dax Formula

[Prashant_123]

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 

[Prashant_123]

Hi @Ahmedx 

Thanks for support now its working 

Can you also please help in power automate ?

Do let me know

[Anonymous]

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

[Prashant_123]

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

[Anonymous]

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

[Prashant_123]

Hi @Ahmedx 

This 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 know

[Ahmedx]

I need a file, I can't do without a file

[Ahmedx]

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)

[Prashant_123]

Hi @Ahmedx  what is calendar here Cannot find table 'Calendar'., can you please check 

[Ahmedx]

[Prashant_123]

Hi @Ahmedx 

1. Can you please check I am attaching the file 

In mY calendar column Date Hierarchy is coming I want to remove date hirearchy 

Rank Measure =

VAR _max = MAX('Calendar_'[Year Month])

VAR _tbl = FILTER(ALL('Calendar_'),'Calendar_'[Year Month]=_max)

RETURN

RANKX(_tbl,[Key],,DESC,Dense)

But still rank is coming 1,1,1 in my file , I am not able to attach power bi file 

[Prashant_123]

also I want Rank monthly wise For example in Jan - 500 is highest amount so that should be rank 1 and 400 rank 2 , in feb 2024 - 300 Rank1 , 245- Rank 2 like this it should work 

@Ahmedx  can you help need to change rank measure 

[Ahmedx]

you must disable hierarchy

You can upload your files to any file sharing service

[Prashant_123]

Hi @Ahmedx I want Rank According to Amount the amount which is highest that should be rank 1 and so on also if month ends like jan 2024 ends then from feb 2024 the rank should start with 1

Rank Measure =

VAR _max = MAX('Calendar_'[Year Month])

VAR _tbl = FILTER(ALL('Calendar_'),'Calendar_'[Year Month]=_max)

RETURN

RANKX(_tbl,[Key],,DESC,Dense)

Can you please let me know 

[Ahmedx]

pls try this

Rn =

VAR _tbl = ALLSELECTED('YOUR TABLE'[CUSTOMER])

RETURN

RANKX(_tbl,[Sales],,DESC,Dense)

[Bibiano_Geraldo]

HI @Prashant_123 ,

Please try the bellow DAX:

Rank Measure = 
RANKX(
    ALL(financials),
    CALCULATE(
        [Sales Amount],
        ALLEXCEPT(financials, financials[Month-Year])
    ),
    ,
    DESC,
    DENSE
)

[Prashant_123]

Hi @Bibiano_Geraldo 

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 option

[Bibiano_Geraldo]

Hi @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.

[Kedar_Pande]

@Prashant_123 

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 LinkedIn

[Prashant_123]

Hi @Kedar_Pande 

Rank_Sales =

VAR CurrentMonth = SELECTEDVALUE('CH - Sales Order By Customer'[Month])

RETURN

RANKX(

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, coun

[Kedar_Pande]

Can you try:

Rank_Sales =

VAR CurrentMonth = SELECTEDVALUE('CH - Sales Order By Customer'[Month])

RETURN

RANKX(

FILTER(

ALL('CH - Sales Order By Customer'),

'CH - Sales Order By Customer'[Month] = CurrentMonth

),

SUM('CH - Sales Order By Customer'[Amount]),

,

DESC,

DENSE

)