presenting unpivot column as percentage in power query

[Christal_Azzam]

Hello all, 

I'm trying to present data that has been converted to unpivoted columns in the power query as percentages instead of numbers. Can anyone support me on that?

[v-jayw-msft · Accepted solution]

Hi @Christal_Azzam ,

What is the expected result?

Column = 'Table'[Value]/SUM('Table'[Value])

Column = 'Table'[Value]/CALCULATE(SUM('Table'[Value]),ALLEXCEPT('Table','Table'[Attribute]))

Best Regards,

Jay

[v-jayw-msft · Accepted solution]

Hi @Christal_Azzam ,

What is the expected result?

Column = 'Table'[Value]/SUM('Table'[Value])

Column = 'Table'[Value]/CALCULATE(SUM('Table'[Value]),ALLEXCEPT('Table','Table'[Attribute]))

Best Regards,

Jay

[Christal_Azzam]

Hello Jay, 

Yes, it's the second one, can you give me the exact steps to produce the same graph?

[v-jayw-msft · Accepted solution]

Hi @Christal_Azzam ,

I did this with DAX.

After unpivoting the columns in Power Query, I created a calculated column as below:

Column = 'Table'[Value]/CALCULATE(SUM('Table'[Value]),ALLEXCEPT('Table','Table'[Attribute]))

 Then I changed the column format to "percentage" and created the visual like below:

Pbix as attachment.

Best Regards,

Jay

[Christal_Azzam]

Thank you!

Exactly what I needed! 

[HenriqueReis]

Hi, 

Please, click on the graph (red) and select this sign in your values (it will be where the blue sign show):

After go to "show value as"

And select percentage.

I hope it helps you

Regards from BR!

[Christal_Azzam]

Hello HenriqueReis, 

In the usual case, this is what I would do, but for this data, since they were 4 columns, unpivoted to give the attribute and the value, in terms of count it's correct but if I covernt to percentage of total, it displays the wrong percentages. 

[HenriqueReis]

ohh, now I understand 

Can I see how the data of the search is in your table?

Maybe we can create something like a SUM of the total and divide each of the three columns by the total, so we are going to have what you want, I think.

[Christal_Azzam]

Sure! So this is the original data on excel: 

I am satisfied with the amount of information shared in the PSEA TOT.	My knowledge of the topic (PSEA) has increased after attending the training.	The duration of the TOT was  adequate.	I was able to follow up and understand what was being presented.	The information provided is important for my role as PSEA focal point.
4	3	4	5	5
3	4	4	4	4
4	4	3	4	4
4	5	4	4	4
4	4	4	5	4
4	5	5	5	5
4	5	2	4	4
4	3	5	5	5
4	4	4	4	5
4	5	4	4	4
4	5	4	3	3
4	4	4	4	4
4	4	4	4	4
4	3	4	4	4
5	5	4	5	5
5	5	3	5	5
4	4	3	4	4
5	5	5	5	5
4	4	4	5	5

So I usually do is add the raw data on power bi - which is a rating from 1 to 5 for each statement on the survey- then I select the columns in the power query and unpivot the columns so that I can combine them into 1 graph showing the rating of each. I managed to show the correct numerical data on the graph, but if I want to convert to percentage, it doesn't show the correct %s. the respondents are a total of 19 persons rating each statement on the survey. 

Hope you can help me. 

[HenriqueReis]

I'm sorry!

This was what I should do, but anyway I think it's not what you want:

I say that beacuse I used different graphs for each "atribute" and after I edited to look like just one graph.

I think there's a way to do what you want, but I'm not finding it on DAX.

Regards!