Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Christal_Azzam
New Member

presenting unpivot column as percentage in power query

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?

Capture2.PNG

Capture.PNG

2 ACCEPTED SOLUTIONS
v-jayw-msft
Community Support
Community Support

Hi @Christal_Azzam ,

 

What is the expected result?

 

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

 

vjaywmsft_0-1654590175472.png

 

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

 

vjaywmsft_1-1654590308432.png

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

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:

vjaywmsft_0-1654681978091.png

Pbix as attachment.

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

9 REPLIES 9
v-jayw-msft
Community Support
Community Support

Hi @Christal_Azzam ,

 

What is the expected result?

 

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

 

vjaywmsft_0-1654590175472.png

 

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

 

vjaywmsft_1-1654590308432.png

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

Hello Jay, 

 

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

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:

vjaywmsft_0-1654681978091.png

Pbix as attachment.

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

Thank you!

Exactly what I needed! 

HenriqueReis
Resolver I
Resolver I

Hi, 

 

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

HenriqueReis_2-1654158662532.png

 

HenriqueReis_0-1654158418826.png

After go to "show value as"

HenriqueReis_1-1654158455967.png

And select percentage.

 

I hope it helps you

 

Regards from BR!

 

 

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. 

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.

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.
43455
34444
44344
45444
44454
45555
45244
43555
44445
45444
45433
44444
44444
43444
55455
55355
44344
55555
44455

 

 

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. 

I'm sorry!

 

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

HenriqueReis_1-1654163390252.png

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!

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.