cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## How to rank one column with 2 categories?

Hey,

I have 2 x categories with products in each and I want to rank product on value but if it is a new category I want this ranking to also start at 1.

From that I also want to add my respective categories to that product list and rank each category as 1.

This is so that I can plot, salad, fruit and their respective products on a graph. Then if I filter on top 3 for example it will show my desired output.

 Table Category Product Value Fruit Apple 9 Fruit Banana 8 Fruit Orange 7 Salad Lettuce 4 Salad Tomato 1 Desired Output NEW Product Value Rank Apple 9 1 Banana 8 2 Orange 7 3 Lettuce 4 1 Tomato 1 2 Fruit 24 1 Salad 5 1
Does anyone have any ideas please?

Lauren.
2 ACCEPTED SOLUTIONS
Super User
You could try:

Use SUMMARIZECOLUMNS to create a new table of your desired output:

Table2=UNION(
SUMMARIZECOLUMNS(Table[Product], "Value", SUM(Table[Value]), "Rank",
RANKX(
ALL('Table'[Category]) ,
CALCULATE(
SUM('Table'[Value])
)
)),
SUMMARIZECOLUMNS(Table[Category], "Value", SUM(Table[Value]), "Rank", 1)

Or this will help get the ranks for the products only:

You could add a column to your table using RANKX, similar to this
Rank all rows as Column (Country) =
RANKX(
FILTER(
‘Table’,
‘Table'[Category] = EARLIER(‘Table'[Category])
),
‘Sales Table'[Value]
)
https://databear.com/how-to-use-the-dax-rankx-function-in-power-bi/

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Community Champion

Hi @Anonymous ,

A little modification to the formula by @AllisonKennedy

``````New Table =
UNION (
SUMMARIZECOLUMNS (
'Table'[Product],
"Value", SUM ( 'Table'[Value] ),
"Rank", IF (
MAX ( 'Table'[Product] )
<> BLANK (),
RANKX (
FILTER (
ALL ( 'Table' ),
'Table'[Category]
= MAX ( 'Table'[Category] )
),
CALCULATE (
SUM ( 'Table'[Value] )
)
)
)
),
SUMMARIZECOLUMNS (
'Table'[Category],
"Value", SUM ( 'Table'[Value] ),
"Rank", IF (
MAX ( 'Table'[Product] )
<> BLANK (),
RANKX (
ALL ( 'Table'[Category] ),
CALCULATE (
SUM ( 'Table'[Value] )
)
)
)
)
)``````

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

3 REPLIES 3
Community Champion

Hi @Anonymous,

Regards FrankAT

Community Champion

Hi @Anonymous ,

A little modification to the formula by @AllisonKennedy

``````New Table =
UNION (
SUMMARIZECOLUMNS (
'Table'[Product],
"Value", SUM ( 'Table'[Value] ),
"Rank", IF (
MAX ( 'Table'[Product] )
<> BLANK (),
RANKX (
FILTER (
ALL ( 'Table' ),
'Table'[Category]
= MAX ( 'Table'[Category] )
),
CALCULATE (
SUM ( 'Table'[Value] )
)
)
)
),
SUMMARIZECOLUMNS (
'Table'[Category],
"Value", SUM ( 'Table'[Value] ),
"Rank", IF (
MAX ( 'Table'[Product] )
<> BLANK (),
RANKX (
ALL ( 'Table'[Category] ),
CALCULATE (
SUM ( 'Table'[Value] )
)
)
)
)
)``````

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Super User
You could try:

Use SUMMARIZECOLUMNS to create a new table of your desired output:

Table2=UNION(
SUMMARIZECOLUMNS(Table[Product], "Value", SUM(Table[Value]), "Rank",
RANKX(
ALL('Table'[Category]) ,
CALCULATE(
SUM('Table'[Value])
)
)),
SUMMARIZECOLUMNS(Table[Category], "Value", SUM(Table[Value]), "Rank", 1)

Or this will help get the ranks for the products only:

You could add a column to your table using RANKX, similar to this
Rank all rows as Column (Country) =
RANKX(
FILTER(
‘Table’,
‘Table'[Category] = EARLIER(‘Table'[Category])
),
‘Sales Table'[Value]
)
https://databear.com/how-to-use-the-dax-rankx-function-in-power-bi/

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Announcements

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors