topic Re: Creating a Rank measure based on measure Net_Sales in Desc Order. in DAX Commands and Tips

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

FischerEric86 — Wed, 14 Feb 2024 00:24:34 GMT

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.

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

Jihwan_Kim — Wed, 14 Feb 2024 03:45:55 GMT

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

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

FischerEric86 — Wed, 14 Feb 2024 15:13:30 GMT

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.

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

Jihwan_Kim — Wed, 14 Feb 2024 18:33:16 GMT

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

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

FischerEric86 — Thu, 15 Feb 2024 01:13:08 GMT

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.

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

FischerEric86 — Thu, 15 Feb 2024 01:48:37 GMT

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.