Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 🙏
Solved! Go to 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.
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)
@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.youtube.com/watch?v=MLrRlPh_ZFQ
Proud to be a Super User!
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?
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
it's worked, but i don't want to calculate the "0" result.
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)
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%)
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |