How to ranking sales with clients and each month

[Lily36876]

Hello everyone,

Originally, I only needed to rank the sales amount BY Client in my report. So, I used this DAX formula:

AMT Rank = RANKX( ALLSELECTED('Sales'[Customer Name]), 'Sales'[1. Actual Sales Amount], , DESC )

But now, I need to view their rankings by YearMonth .

How should I modify my formula for this?

Please provide me with some advice. Thank you.

My ideal format looks like the following: 

And this is a simple data model

[BeaBF]

@Lily36876 Try with:

AMT Rank =
RANKX(
FILTER(
ALLSELECTED('Sales'[Customer Name]),
'Sales'[YearMonth] = MAX('Sales'[YearMonth])
),
'Sales'[1. Actual Sales Amount],
,
DESC
)


BBF

[Lily36876]

Hello!!! Thanks for reply~

I'm try it,but my 'Sales'[YearMonth] = MAX('Sales'[YearMonth]) show error,

because 'Sales'[YearMonth]  properties is column not a measure.

[BeaBF]

@Lily36876 the code is for a calculated olumn, not a measure. Try.

BBF

[VahidDM]

Hi @Lily36876 

As your data model is the Sales Table filter the Date table?

Can you share sample of your data in text format or PBIX file here?

What is the expected output? in the ideal image you shares, the rak is 1 for all items

BTW, check this, it's quick similar to your case:https://community.fabric.microsoft.com/t5/Desktop/Rank-sales-value-grouped-by-month-and-year/m-p/330...

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube