Create a new column with Rank with Multiple Columns and group by

[yellow43]

Hi Fabric Community,

I need help with following: 

Customer	country	product	qty	AVG sales
ALEX	Germany	A1	0
ALEX	Greece	A1	0
ALEX	Greece	B2	0
ALEX	Spain	C3	55	680,00 €
ALEX	Spain	B2	0
ALEX	Spain	A2	72	100,00 €
ALEX	Spain	A1	420	520,00 €

Expected Output:

Customer	country	product	qty	AVG sales	Rank measure:
ALEX	Spain	C3	55	680,00 €	3
ALEX	Spain	A2	72	100,00 €	1
ALEX	Spain	A1	420	520,00 €	2

Conditions:

1. same customer, same country, different product should rank by qty (1st criteria) and then by avg_sales (2nd criteria)

2. ignore all null values and zero values in columns [qty] and [AVG sales]

I have already following measure:

Rank measure: =
IF (
HASONEVALUE ( 'Table'[country] ),
CALCULATE (
COUNTROWS ( 'Table' ),
WINDOW (
1,
ABS,
4,
REL,
SUMMARIZE (
ALL ( 'Table'),
'Table'[country],
'Table'[product],
'Table'[qty], 'Table'[AVG Sales]

),
ORDERBY ( 'Table'[qty], ASC, 'Table'[AVG Sales], ASC ),
DEFAULT,
PARTITIONBY ( 'Table'[country])
)
)
)

But is not ignoring null and zero values. 

Can you help me?

Thank you!

[Anonymous]

Hi @yellow43 ,

If I understand correctly, the issue is that you want to create a new column with Rank. Please try the following methods and check if they can solve your problem:

1.Create the simple table.

2.Create a new measure to rank. Enter the following DAX formula.

00_Rank = if(SUM('Table'[AVG sales])>0, RANKX(FILTER(ALLSELECTED('Table'),'Table'[qty]>0),CALCULATE(SUM('Table'[AVG sales])),,ASC,Dense) )

3.Drag the measure to the table visual.

4.The result is shown below.

Best Regards,

Wisdom Wu

[DallasBaba · Accepted solution]

@yellow43 to create a new column with rank based on multiple columns and group by, you can use the following DAX formula:

Rank measure = 
IF (
    HASONEVALUE ( 'Table'[country] ),
    CALCULATE (
        COUNTROWS ( 'Table' ),
        WINDOW (
            1,
            ABS,
            4,
            REL,
            SUMMARIZE (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[qty] > 0 && 'Table'[AVG sales] > 0
                ),
                'Table'[country],
                'Table'[product],
                'Table'[qty],
                'Table'[AVG sales]
            ),
            ORDERBY (
                'Table'[qty],
                ASC,
                'Table'[AVG sales],
                ASC
            ),
            DEFAULT,
            PARTITIONBY ( 'Table'[country] )
        )
    )
)

This formula will ignore all null and zero values in columns [qty] and [AVG sales]. It will rank the rows based on the criteria you specified: same customer, same country, different product should rank by qty (1st criteria) and then by  AVG sales (2nd criteria).

Please note that the formula is case-sensitive and the column names must match exactly with the ones in your table. Also, make sure to replace 'Table' with the name of your table.

Let me know if this works for you. @ me in replies, or I'll lose your thread!!!  
Note:

If this post is helpful, please mark it as the solution to help others find it easily. Also, if my answers contribute to a solution, show your appreciation by giving it a thumbs up! 

[yellow43]

Hello @DallasBaba , 

Big Help!

Thank you so much!

[DallasBaba]

@yellow43 I am happy to know it works.  Kindly @ me with any future needs around Power BI and Fabric.

Please give it a kudos by clicking the Thumbs Up! It goes a long way to helping others find this solution quickly.