Measures or table to determine the rank of column results within a row.

[Anonymous]

I have data arranged as follows:

Table 7

Index	Brand A	Brand B	Brand C	Brand D
1	51-100	101-150
2	151-200	51-100	0-50	151-200
3		0-50		0-50
4	0-50	101-150		101-150

Required 

Index	Brand	Value	Rank
1	Brand B	101-150	1
1	Brand A	51-100	2
2	Brand A	151-200	1
2	Brand D	151-200	1
2	Brand B	51-100	2
2	Brand C	0-50	3
3	Brand B	0-50	1
3	Brand D	0-50	1
4	Brand B	101-150	1
4	Brand D	101-150	1
4	Brand A	0-50	2

Would this be possible? 

I am a new user so step by step instructions would be appreciated.

Thanks

Jo

[HotChilli · Accepted solution]

In Power Query, select all the columns apart from Index and choose Unpivot Columns from the Transform menu.

You then filter the Value column to remove blank rows (click on the down arrow in the Value header and uncheck 'blank')

You should Duplicate the column Value to preserve it in it's original form (right-click the header and choose Duplicate).

Then right-click the Value-Copy column and choose Split Column-> by delimiter (it should select '-' by default)

This will give you a numeric column which you can use in DAX to rank the data.

Close and Apply

In Power Bi, Add a calculated column to the table with DAX similar to the following

Rank within Index = VAR _index = 'Table'[Index]
RETURN
    RANKX(
        FILTER(
                'Table',
                'Table'[Index] = _index
                ),
        'Table'[Value - Copy.1],,,Dense 
        )

That's some reasonably complicated DAX for a beginner but basically it compares the values of column [Value - Copy.1] within each Index category and gives them a rank.

The column and table names may be different at your side so you'll have to sub them in.

[amitchandak]

You need to unpivot. Using edit query. In new office ribbon it is know as transform data

Refer

https://radacad.com/pivot-and-unpivot-with-power-bi

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Anonymous]

@amitchandak Thank you for this, I will apply the unpivot. What should I use to determine the rank?

[HotChilli · Accepted solution]

In Power Query, select all the columns apart from Index and choose Unpivot Columns from the Transform menu.

You then filter the Value column to remove blank rows (click on the down arrow in the Value header and uncheck 'blank')

You should Duplicate the column Value to preserve it in it's original form (right-click the header and choose Duplicate).

Then right-click the Value-Copy column and choose Split Column-> by delimiter (it should select '-' by default)

This will give you a numeric column which you can use in DAX to rank the data.

Close and Apply

In Power Bi, Add a calculated column to the table with DAX similar to the following

Rank within Index = VAR _index = 'Table'[Index]
RETURN
    RANKX(
        FILTER(
                'Table',
                'Table'[Index] = _index
                ),
        'Table'[Value - Copy.1],,,Dense 
        )

That's some reasonably complicated DAX for a beginner but basically it compares the values of column [Value - Copy.1] within each Index category and gives them a rank.

The column and table names may be different at your side so you'll have to sub them in.

[Anonymous · Accepted solution]

@HotChilli Fantastic! Thank you for answering this for me. Worked perfectly x.