Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
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
@amitchandak Thank you for this, I will apply the unpivot. What should I use to determine the rank?
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.
User | Count |
---|---|
91 | |
73 | |
68 | |
63 | |
55 |
User | Count |
---|---|
98 | |
89 | |
74 | |
67 | |
62 |