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
Anonymous
Not applicable

Calculate percentage %

Hello PowerBI Community 😁

 

I'm new with PowerBI, query, & Stuffs. & I need help with something.

 

So, i want to calculate this column, into percentage %

nymph056_3-1595933367982.png

The logic is, i want to divide every result with /20 and change it to percentage %, but i dont want to calculate "none" result. 
for example: if the result is "20" it's should be "100%"

I've tried this, made a new measurement, but it's not work for me.

nymph056_1-1595932744453.png
*Info
Before i make the safety column, i've to define every result & adding new column with this,

nymph056_4-1595933576444.png

 

Should i convert this column first into interger or something? then i can make a new measurement from it?

 

Any help will be appreciated 🙏

1 ACCEPTED SOLUTION

Hi @Anonymous :

 

Create a column as below:

column = 
var _convert=IF('Table'[Safety(20)]="None",0,'Table'[Safety(20)])
Return
DIVIDE(CONVERT(_convert,INTEGER),20)

And you will see:

Annotation 2020-07-29 151733.png

See attached for the related .pbix file.

 


 
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
 

View solution in original post

9 REPLIES 9
harshnathani
Community Champion
Community Champion

Hi @Anonymous ,

 

 

IF [Safety] is a measure

 

New Measure = 

IF (MAX('Table[Safety]) = "None", BLANK(), CONVERT(MAX(Table[Safety]),INTEGER)

 

 

If [Safety] is a Column

 

New Column = IF ('Table[Safety] = "None", BLANK(), CONVERT(Table[Safety],INTEGER)

 

 

Then change the formatting to Percentage from the top ribbon.

 

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

amitchandak
Super User
Super User

@Anonymous , in power query, replace None with blank and then convert the data type to whole number or decimal.

 

That is there on right click of table

https://www.biinsight.com/quick-tips-conditionally-replace-values-based-on-other-values-in-power-query/

https://www.youtube.com/watch?v=MLrRlPh_ZFQ

vanessafvg
Super User
Super User

you will need to convert it into an integer if you want to do calculations on it, or a number data type, it cannot work with mixed types in a calculation, consider duplicating the column and changing none to 0 if that fits in with your logi




If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Anonymous
Not applicable

@vanessafvg 

I've changed the "None" with 0 and changed the type into "whole Number"

nymph056_1-1595935660555.png

 

Then, when i get back to my new measurement its still give me this error

nymph056_2-1595935835435.png

 

nymph056_3-1595936334106.png

 

how do i fix this?

Hi @Anonymous ,

 

Staff KRa - Safety2 = IF(MAX('Productivity and Task Organization[Staff KRA - Safety New]) = 0, BLANK(), DIVIDE(MAX('Productivity and Task Organization[Staff KRA - Safety New]), 20)

 

Regards,

Harsh Nathani

Anonymous
Not applicable

Hi @harshnathani 

 

it's worked, but i don't want to calculate the "0" result. 

nymph056_0-1595937383266.png

 

is there a way to exclude "0" from calculation?

 

Hi @Anonymous ,

 

Please share the Calculations of the Colummns and measures you have used in the visualization.

 

This should work.

 

Staff KRa - Safety2 = IF(MAX('Productivity and Task Organization[Staff KRA - Safety New]) = 0, 0, DIVIDE(MAX('Productivity and Task Organization[Staff KRA - Safety New]), 20)

Anonymous
Not applicable

Thanks @harshnathani 

 

It's still same. it's still 100% in "0" result.

This is the new column that i use for %

nymph056_0-1595939220414.png

This is the [Safety NEW] column, and i've changed the data type to "Whole Number"

nymph056_1-1595939282149.png

And this is [Safety] Column, in here i've to define all. The data type is "Text"

nymph056_2-1595939375178.png

if i do this manually, Example: if the user got 13, then (13/20 * 100%) = 65%, and if the user got 20, it should be 100% (20/20 * 100%)

 

 

 

Hi @Anonymous :

 

Create a column as below:

column = 
var _convert=IF('Table'[Safety(20)]="None",0,'Table'[Safety(20)])
Return
DIVIDE(CONVERT(_convert,INTEGER),20)

And you will see:

Annotation 2020-07-29 151733.png

See attached for the related .pbix file.

 


 
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
 

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.