topic Re: How to ranking sales with clients and each month in DAX Commands and Tips

How to ranking sales with clients and each month

Lily36876 — Fri, 03 Nov 2023 05:05:11 GMT

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

Re: How to ranking sales with clients and each month

VahidDM — Fri, 03 Nov 2023 05:13:10 GMT

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/3304490

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

Re: How to ranking sales with clients and each month

BeaBF — Fri, 03 Nov 2023 07:39:34 GMT

Lily36876 Try with:

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

BBF

Re: How to ranking sales with clients and each month

Lily36876 — Fri, 03 Nov 2023 07:55:00 GMT

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.

Re: How to ranking sales with clients and each month

BeaBF — Mon, 06 Nov 2023 16:03:52 GMT

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

BBF