How to rank one column with 2 categories?

[Anonymous]

Hey,

Could someone please help me out with the following problem please?

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?

Thank you in advance,

Lauren.

[AllisonKennedy · Accepted solution]

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)

https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

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/

[FrankAT]

Hi @Anonymous,

what about the following solution:

Regards FrankAT

[harshnathani · Accepted solution]

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)

Did I answer your question? Mark my post as a solution!

[AllisonKennedy · Accepted solution]

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)

https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

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/