Calculate percentage %

[Anonymous]

Hello PowerBI Community 😁

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

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

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.

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

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

Any help will be appreciated 🙏

[harshnathani]

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]

@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]

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

[Anonymous]

@vanessafvg 

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

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

how do i fix this?

[harshnathani]

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]

Hi @harshnathani 

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

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

[harshnathani]

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]

Thanks @harshnathani 

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

This is the new column that i use for %

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

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

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%)

[v-kelly-msft · 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:

See attached for the related .pbix file.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!