Grouping table and sort by 2 columns with ID per group

[jppuam]

Hello,

im trying to create a table that has merchandise, total sells and the country/product/year column.

what i need to do is sort DESC the "Country/Product/Year" and then also sort Sells DESC.

After that i need to creat an index column for each "Country/Product/Year", just like the image shows.

i've figure several methods, but im not reaching what i want. Does anyone knows how to implement this ?

thanks,

JR

[v-zhangtin-msft · Accepted solution]

Hi, @jppuam 

You can try the following methods.

Column = CALCULATE(COUNT('Table'[Merchandise]),FILTER('Table',[Country/Product/Year]=EARLIER('Table'[Country/Product/Year])&&[Sells]>=EARLIER('Table'[Sells])))

Is this the result you expected?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Do you want a calculated column or a measure?  Also, share data in a format that can be pasted in an MS Excel file.

[audreygerred]

In my test data I created something similar. I have the rank for SKUs based on Order Quantity. When I make a visual with the SKU number and the year, the rank starts over again each new year. My model is in a star schema with a product table, a date table, and a fact table.

Sales Rank = RANKX(ALL('Product'), [Order Quantity], , DESC,Dense) If you want the ranking to respect the filters you or a user might have in place, use ALLSELECTED instead of ALL

[jppuam]

thanks, but if you want to sort (in your table) by SKU and Fiscal Year ?

I dont know if it was random, because they are sorted by that.

JR

[audreygerred]

The table I made for an example is sorted by Year in Desc order and Qty sold (which matches rank) in Desc order because that closely matched what you had with the years sorted and the qty...

However, you can sort by anything you want and the rank will still be correct (it just looks funky). Below, I have it sorted by SKU. We can see that the rank from my earlier screen shot is still the rank here because rank is based on qty sold. The ordering of the table viz itself is based on how you want it.