Forum Discussion
Solved
RankX and filter certain amounts
Hi, I want to rank certain things and exclude something which is below threshold. This below formulas works fine. Product Marketing Brand asc =
VAR PRODCUTRANKX =
RANKX (
ALL ( 'Pr...
Hi Ritesh_Air ,
Would you please refer to the following measure:
Product Marketing Brand asc = VAR PRODCUTRANKX = IF ( [Net Sales] > 1000, RANKX ( FILTER ( ALL ( 'Product'[Product Marketing Brand] ), [Net Sales] > 1000 ), CALCULATE ( [Net Sales] ), , ASC, DENSE ), BLANK () ) RETURN PRODCUTRANKXIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Thanks Parry. But my actual measure "Net Sales" is here, which is a combination of a using 2 tables.
Net Sales =
CALCULATE (
SUM ( 'Sales Order Detail'[Net Price Amount] ),
'Sales Order Detail'[Open Order Indicator] = 0
)From there I am creating "Net Sales CY" as:
Net Sales CY =
var _Year = CALCULATE(MAX('Date'[Year Key]),ALL('Date'))
return
CALCULATE([Net Sales], 'Date'[Year Key] = _Year)Then I am ranking as:
with this formula:
Product Marketing Brand asc =
VAR PRODCUTRANKX =
RANKX (
ALL ( 'Product'[Product Marketing Brand] ),
CALCULATE ( [Net Sales]),
,
ASC,
DENSE
)
RETURN
PRODCUTRANKX
Now as my original question:
If I want to filter my Rank table as "Net Sales" > 1000, how do I do that?
Thanks,
Ritesh
Hi Ritesh_Air ,
Would you please refer to the following measure:
Product Marketing Brand asc =
VAR PRODCUTRANKX =
IF (
[Net Sales] > 1000,
RANKX (
FILTER ( ALL ( 'Product'[Product Marketing Brand] ), [Net Sales] > 1000 ),
CALCULATE ( [Net Sales] ),
,
ASC,
DENSE
),
BLANK ()
)
RETURN
PRODCUTRANKX
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai