Creating Dense Ranks for Multiple Categories using M Query or Dax

[VidyaSagarMalla]

Hello All,

I am trying to figure how the acheive the Dense Ranks for the below sample data. I was able to get the Rank right for category level per Year but when i am comibing for other columns ranks reuslts are NOT coming right. 

Expression used in M : = Table.Group(#"Removed Columns", {"Category","Years"}, {{"All", each Table.AddRankColumn(_,"Fixed Rank",{"Price",Order.Ascending},[RankKind = RankKind.Dense])}})

I would love to see whats the best approach to get the below result. Thanks in Advance for looking into this.

Actual Data	Transformation Columns in Power BI
Index	Class	Categroy	Product	Year	Price	Product Rank Per Year	Rank by Category Per Year	Rank by  Class Per Year	Rank by Index Per Year
Active	Equity	Fruits	Apple Gala	2022	$                1.00	1	1	2	1
Active	Equity	Fruits	Apple Pink	2022	$                2.00	2	1	2	1
Active	Equity	Fruits	Grapes	2022	$                3.00	3	1	2	1
Active	Equity	Veg	Carrots	2022	$                2.00	1	2	2	1
Active	Equity	Veg	onions	2022	$                3.00	2	2	2	1
Active	Equity	Veg	Brocli	2022	$                4.00	3	2	2	1
Active	Fixed Income	Beverages	Pepsi	2022	$                3.00	1	3	1	1
Active	Fixed Income	Beverages	Dew	2022	$                5.00	2	3	1	1
Active	Fixed Income	Beverages	coke	2022	$                6.00	3	3	1	1

[techies]

Hi @VidyaSagarMalla you can add measures like this

[v-achippa · Accepted solution]

Hi @VidyaSagarMalla,

Thank you for reaching out to Microsoft Fabric Community.

The power query method using Table.AddRankColumn only works per one grouping at a time. So if you want multiple dense rank columns we have to repeat the grouping and ranking logic separately for each combination. So use DAX calculated columns in power bi.

Rank by Category Per Year =

RANKX(

    FILTER('YourTable',

        'YourTable'[Year] = EARLIER('YourTable'[Year]) &&

        'YourTable'[Categroy] = EARLIER('YourTable'[Categroy])

    ),

    'YourTable'[Price], ,

    ASC,

    DENSE

)

You can simply apply similar formulas for other combinations like Product, Class, and Index.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @VidyaSagarMalla,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.
If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[Ashish_Excel]

Hi,

Would you be OK with a measure based solution?

[VidyaSagarMalla]

Thank for looking into this @pankajnamekar25 . But looks like you have provided the same query from my question.  

[pankajnamekar25]

Hello @VidyaSagarMalla 

You can try for this in PQ

But I will suggest use DAX measure

Table.Group(#"Previous Step", {"Category","Year"}, {

    {"All", each Table.AddRankColumn(_, "CategoryYearRank", {"Price", Order.Ascending}, [RankKind = RankKind.Dense])}

})

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.