topic Re: Want to write rank by each month per area DAX in DAX Commands and Tips

Want to write rank by each month per area DAX

Anonymous — Sat, 18 Dec 2021 18:39:54 GMT

Hi Team,

I need to calculate rank by month per each area. If there is any null value/no value in sales column will asign lowset rank.

Power BI Link: https://1drv.ms/u/s!Au-aOkl1BoHujVmkmN9Ws9yFCDSk?e=LQfhyt

Sample Data

Having first 3 columns are source columns , Rank-output which we need to generate , In the attached one drive file having original data posting requirement with sample data

Output Visual:

Thanks In Advance

Can you please help me out on this ?

Re: Want to write rank by each month per area DAX

AlexisOlson — Sat, 18 Dec 2021 19:25:33 GMT

Ranking over Sheet1 isn't quite what you want since each area can have multiple rows per month.

Try this instead:

RANKX ( ALL ( Sheet1[Area] ), CALCULATE ( SUM ( Sheet1[Sales] ), ALLEXCEPT ( Sheet1, Sheet1[YearMonth], Sheet1[Area] ) ) )

Re: Want to write rank by each month per area DAX

Anonymous — Sun, 19 Dec 2021 07:26:54 GMT

Hi AlexisOlson ,

Thanks for the quick response. I am getting rank now but want to showcase rank to null values also.

For example:

We want to assign maxrank+1 to all null values.

In the below example, We don't have sales in the area of C& K (Apr-2021) but we need to assign rank to all null values i.e.maxrank+1(15+1)

Assign Rank to null values

Power BI Refrence Link: https://1drv.ms/u/s!Au-aOkl1BoHujVmkmN9Ws9yFCDSk?e=rEW41e

Thanks in advance

Re: Want to write rank by each month per area DAX

AlexisOlson — Sun, 19 Dec 2021 19:05:58 GMT

In order to show anything in these cells, I'd recommend creating a dimension table for [Area].

(See attached.)

Then you can define blank to be a negative number like this:

RANKX ( ALL ( Sheet1[Area] ), VAR SumSales = CALCULATE ( SUM ( Sheet1[Sales] ), ALLEXCEPT ( Sheet1, Sheet1[YearMonth], Sheet1[Area] ) ) RETURN IF ( ISBLANK ( SumSales ), -1, SumSales ) )

Re: Want to write rank by each month per area DAX

Anonymous — Mon, 20 Dec 2021 05:13:10 GMT

Hi AlexisOlson ,

Awesome.This is working as expected But i want to know how it works?

i mean

1.why we need to use area column from derived PBI table instead of existing one ?(What is the difference between these two columns)

2. How negative value (i.e. in if condition we mention -1) giving maxrank+1 ?

Thanks in Advance.

Re: Want to write rank by each month per area DAX

AlexisOlson — Mon, 20 Dec 2021 15:23:55 GMT

The problem with the existing table is that there are no rows for "c" and "k" in Apr-21, so you run into an auto-exist problem where those cells won't show anything no matter how you define the measure.

I picked -1 rather arbitrarily. I assumed the smallest non-blank value would be zero and picked something less than that. Feel free to pick your favorite negative number instead.