RANKX by two columns

[Anonymous]

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?

[v-shex-msft]

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

[amitchandak]

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