Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

RANKX by two columns

Hi,

 

I'm trying to rank my data making use of 2 dimensions that is 'Product Family' and 'Fiscal Qtr' since I need the top 10 values based on the Fiscal Qtr. I'm trying to put this measure on the visual filter.

 

I created a measure to rank all data irrespective of the fiscal quarter using

 

		Rank_WITH_ALL =
		RANKX (
			ALL ( AIO_Sample[Product Family] ),
			CALCULATE ( [ASP Impact_Latest] ),
			,
			ASC,
			SKIP
		)

 

The result:

 

 

Now when I try to pass the Fiscal Quarter="FY2021Q4 condition using

Rank_WITH_ALL2 =
RANKX (
    FILTER (
        ALLSELECTED ( AIO_Sample[Product Family], AIO_Sample[Fiscal Qtr] ),
        AIO_Sample[Fiscal Qtr] = "FY2021Q4"
    ),
    CALCULATE ( [ASP Impact_Latest] ),
    ,
    DESC,
    DENSE
)

 

The Result: I get 15 Product Families even though I selected "10" in the filter. What am I doing wrong?

2 Replies

  • Anonymous , Try like

     

    Rank_WITH_ALL2 =
    RANKX (
    ALLSELECTED ( AIO_Sample[Product Family], AIO_Sample[Fiscal Qtr] ),
    CALCULATE ( [ASP Impact_Latest] , filter(AIO_Sample, AIO_Sample[Fiscal Qtr] = "FY2021Q4" )),
    ,
    DESC,
    DENSE
    )

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Anonymous,

    Can you please share some dummy data with a similar data structure and expected results? It should help us clarify your scenario and test to coding formula.

    How to Get Your Question Answered Quickly  

    Regards,

    Xiaoxin Sheng