Can I use Rank if my data is not in a table/tabular form?

[DessiB]

Hi, 

Situation:

I have survey data, from which I'm trying to create a PowerBI Dashboard. The Dashboard has 10 (!) demographic slicers. The source data is flattened by respondant. There are approximately 50 questions (of various complexities).

Problem:

I'm trying to rank a particlar survey question by the % of responses. The problem is that each response is in a separate column. I'm also choosing to display as a MultiRow Card instead of a Table/Matrix viz, as those viz display the responses horizontally. 

Questions:

Is there a way to rank the values in a multirow card?

Is the a calculated measure I can create to provide me with a rank? 

Due to the shape and size of the data, having a solution to this would be amazing. I'm willing to test other workarounds, so long as they assist me in sorting groups of calculated measures dynamically.

Cheers

[AllisonKennedy]

Surveys are great fun in Power BI - I typically split them into multiple tables, so start with your flat survey data as the 'Raw Data' then right click on that in Query Editor and reference it, to create a new query. Create a few tables as follows:
DimRespondents table - should have 1 row per respondent along with their demographic info, and can have answers to a few of the simple questions that only have 1 row and column (usually these are demographic, email, company name, etc).

QuestionTable for each of the types of questions (likert, multi response, the example you have above, etc). This table should have three columns:
RespondentID
Question
Response
To achieve this, remove all other columns (for demographics, etc that are already in the DimRespondents table).
UNPIVOT the columns in Power Query, so in your example above you might be better off using the Unpivot Other Columns option after you select RespondentID

Then Close & Apply and create relationship and visualizations.

If you provide sample data I can provide you with the pbix or M code to show what I mean.

[DessiB]

I knew it would come back to this! 😅 

My rookie enthusiasm took me down Easy Street instead of doing a proper table relationship. I'll do a u-turn and not get excited over survey data! 

Thanks Allison 🙂 

[V-lianl-msft]

Hi @DessiB ,

Refer to this:

https://community.powerbi.com/t5/Desktop/Custom-Sort-for-Multi-Row-Card/td-p/527838 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

Hard to visualize your data, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[amitchandak]

@DessiB , I can see sort option on column/group by or on the measure in the multi-row card in three dots. if that do not work you have to create sort column and use.

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column

[DessiB]

Thanks for your suggestion.

Unfortunately I'd already looked into this option and the sorting options don't suit as they don't allow me to sort by the numerical total of the columns, just the column itself.

Could adding a calculated measure work?