Want to write rank by each month per area DAX

[Anonymous]

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 ?

[AlexisOlson]

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] )
    )
)

[Anonymous]

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

[AlexisOlson · Accepted solution]

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 )
)

[Anonymous]

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.

[AlexisOlson]

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.