Creating a Rank measure based on measure Net_Sales in Desc Order.

[FischerEric86]

In my table I have columns DBA which is a list of customer names, and then NET_SALES. 

I created the measure: Net_Sales(m) =

IF(ISBLANK(CALCULATE(SUM(table[NET_SALES]))), 0, CALCULATE(SUM(table[NET_SALES])))

I now want to create a new measure, Rank, which ranks the Net_Sales(m) in descending order.

The issue I'm having is that when it gets to 0.00 or less, into the negatives, the ranking jumps all over in no particular order.  

I aslo need to create a new measure that then looks at the Rank and divides the results into four even quads.  The top 25% of the Rank would be quad 1, the next 25% quad 2, etc.  

[FischerEric86]

I created a MaxRank measure

MaxRank = MAXX(ALLSELECTED(table[DBA]), [Rank],TRUE)

Then another measure to take Rank / MaxRank.  This gave me my % of rank and allows me to then create a measure that groups by % of Rank into 4 quads. 

[FischerEric86]

Do you have a solution on how to create the last measure where it looks at the Rank net sales measure and evenly divides the results into four quadrants based ona descening order?  So if there's 100 rows, the first 1-25 rows that would have the highest net sales would be labeled as Quad 1, 26-50 as Quad 2, 51-75 as Quad 3, and 76-100 as Quad 4.  

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file if it suits your requirement.

expected result measure: =
VAR _all =
    COUNTROWS ( ALL ( Data[customer_name] ) )
VAR _rank = [Rank net sales measure]
RETURN
    IF (
        HASONEVALUE ( Data[customer_name] ),
        SWITCH (
            TRUE (),
            DIVIDE ( _rank, _all ) <= 0.25, 1,
            DIVIDE ( _rank, _all ) <= 0.5, 2,
            DIVIDE ( _rank, _all ) <= 0.75, 3,
            4
        )
    )

[FischerEric86]

I've used a solution like this before and ran into the same issue I see here.   Customer's F-S are grouped as 2, where F-K or F-I should be 2, and then K-S or I-S should be grouped as 3.  So that we have four groupings evenly split or as close to evenly split.  

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

Net Sales measure = 
VAR _netsales =
    SUM ( Data[net_sales] )
RETURN
    IF ( ISBLANK ( _netsales ), 0, _netsales )

RANK function (DAX) - DAX | Microsoft Learn

Rank net sales measure =
RANK (
    SKIP,
    ALL ( Data[customer_name] ),
    ORDERBY ( [Net Sales measure], DESC )
)