Ranking For few columns

[ramshoney1225]

Hi 

I have a sample below table, which i hav the data this way.

Productname	Subcategory	revenue
Coolers	homecooler	345
Refregirator	Mini refregiration	543
Fulltotal	Total	456
Bikes	Bicycles	887
Cars	Battery vehicles	667

Now i want to create two rank

1.) based on Revenue for Product category

2.) based on Revenue for subcategory

When i create a RANK for Product category, my condition is "Fulltotal" should not be included in the Rank which always need to come last

When i create a RANK for Subcategory, my condition is "Total" should not be included in the Rank which always need to come last

SO my out put should be like this , Even though Total is high it is coming as Last, How to write the DAX for the below

Productname	Subcategory	revenue	RankProd	RankSub
Bikes	Bicycles	887	1	1
Cars	Battery vehicles	667	2	2
Refregirator	Mini refregiration	543	3	3
Coolers	homecooler	345	4	4
Total	Total	456	5	5

Thanks,

[Greg_Deckler]

@ramshoney1225 Try:

Column = 
    VAR __Count = COUNTROWS('Table5')
RETURN
    IF([Productname]="Fulltotal",__Count,RANKX(FILTER(ALL('Table5'),[Productname]<>"Fulltotal"),[revenue],,DESC))

Column = 
    VAR __Count = COUNTROWS('Table5')
RETURN
    IF([Subcategory]="Total",__Count,RANKX(FILTER(ALL('Table5'),[Subcategory]<>"Total"),[revenue],,DESC))

[amitchandak]

@ramshoney1225 , Try measures like, assume revenue is a measure

Product Rank =

rankx(allselected(Table[product name]), [revenue])

Sub category Rank=

rankx(allselected(Table[subCategory name]), [revenue])

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale