topic How to transform a number into a percentage without using the format func. in DAX Commands and Tips

How to transform a number into a percentage without using the format func.

Anonymous — Thu, 09 Mar 2023 15:02:13 GMT

I´ve been strugguling for the past days and I can't find the answer anywhere.

on my p&L table I have a color format that if the value is below 0 then is red otherwise is blue.
Now when I change the number values into percentage then it convert it to string values which causes that my actual color:

Actual Color = IF([Actual]>=0.0,"#118DFF",IF([Actual]<0.0, "#D64550"))

Makes them blue which makes sense beacuse then again is not a value is a string.
In the column is a mix between numbers and percentages so the code is as following to calculate the percentages and get the color right.

Actual = VAR var1 = IF(HASONEVALUE('Sort Account Type'[Account_Type]),

VALUES('Sort Account Type'[Account_Type]),BLANK())

return

SWITCH(var1,

"Margin Sales New",if([Margin Sales New]<> BLANK(),CONVERT([Margin Sales New], DOUBLE)*100,BLANK()),

"Margin Sales Used Direct",if([Margin Sales Used Direct]<> BLANK(),CONVERT([Margin Sales Used Direct],DOUBLE)*100 ,BLANK()),

"Margin Sales Used RV",if([Margin Sales Used RV]<> BLANK(),CONVERT([Margin Sales Used RV],DOUBLE)*100,BLANK()),

"Margin Rental incl Dep.",if([Margin Rental incl Dep.]<> BLANK(),CONVERT([Margin Rental incl Dep.],DOUBLE)*100,BLANK()),

"Margin Lease incl Dep.",if([Margin Lease incl Dep.]<> BLANK(),CONVERT([Margin Lease incl Dep.],DOUBLE)*100,BLANK()),

"Margin Damage",if([Margin Damage]<> BLANK(),CONVERT([Margin Damage],DOUBLE)*100,BLANK()),

"Margin Sales Parts",if([Margin Sales Parts]<> BLANK(),CONVERT([Margin Sales Parts],DOUBLE)*100,BLANK()),

"Margin Transport",if([Margin Transport]<> BLANK(),CONVERT([Margin Transport],DOUBLE)*100,BLANK()),

"Net Gross Margin",if([Margin]<> BLANK(),CONVERT([Margin],DOUBLE)*100,BLANK()),

"Gross Profit",[Gross Profit],SUM('P&L'[KUSD]))

BUT THE PERCENTAGE SIGN DOES NOT APPEAR ON THE CELL AND I WANT TO KNOW HOW.

Pls Help 😣

Re: How to transform a number into a percentage without using the format func.

amitchandak — Fri, 10 Mar 2023 01:53:10 GMT

Anonymous , first of there is no need to multiply it by a hundred, keep ratio and use the option in the measure/column tool with the % symbol

Re: How to transform a number into a percentage without using the format func.

Anonymous — Fri, 10 Mar 2023 20:55:04 GMT

The thing is that in the column I have other values that aren't percentages. Thats why for those specific values I multply by a 100. Because if I use Format(value,"Pecentage") then it transform it to a string and my Actual color does not identify the values smaller than 0. and even if it's a negative it makes them blue insetad of red for those negative percentage values.

Re: How to transform a number into a percentage without using the format func.

ppm1 — Sat, 11 Mar 2023 13:35:29 GMT

I would consider setting a conditional format string using a calculation group for this.

Controlling Format Strings in Calculation Groups - SQLBI

Pat

Re: How to transform a number into a percentage without using the format func.

Anonymous — Mon, 13 Mar 2023 21:13:58 GMT

Thanks ppm1 & amitchandak for your replies.

The soltion that I found was as following:

Actual = VAR var1 = IF(HASONEVALUE('Sort Account Type'[Account_Type]),VALUES('Sort Account Type'[Account_Type]),BLANK())

RETURN

SWITCH(var1,

"Margin Sales New",IF([Margin Sales New]<>BLANK(), FORMAT([Margin Sales New], "0.0%"), BLANK()),

"Margin Sales Used Direct",IF([Margin Sales Used Direct]<>BLANK(), FORMAT([Margin Sales Used Direct], "0.0%"), BLANK()),

"Margin Sales Used RV",IF([Margin Sales Used RV]<>BLANK(), FORMAT([Margin Sales Used RV], "0.0%"), BLANK()),

"Margin Rental incl Dep.",IF([Margin Rental incl Dep.]<>BLANK(), FORMAT([Margin Rental incl Dep.], "0.0%"), BLANK()),

"Margin Lease incl Dep.",IF([Margin Lease incl Dep.]<>BLANK(), FORMAT([Margin Lease incl Dep.], "0.0%"), BLANK()),

"Margin Damage",IF([Margin Damage]<>BLANK(), FORMAT([Margin Damage], "0.0%"), BLANK()),

"Margin Sales Parts",IF([Margin Sales Parts]<>BLANK(), FORMAT([Margin Sales Parts], "0.0%"), BLANK()),

"Margin Transport",IF([Margin Transport]<>BLANK(), FORMAT([Margin Transport], "0.0%"), BLANK()),

"Net Gross Margin",IF([Margin]<>BLANK(), FORMAT([Margin], "0.0%"), BLANK()),

"Gross Profit",[Gross Profit],SUM('P&L'[KUSD]))

Actual Color =

VAR actual = [Actual]

VAR actual_num =

IF(ISBLANK(actual), BLANK(),

VALUE(

SUBSTITUTE(

SUBSTITUTE(actual, ",", "."),

".", ""),

"%", "")

)

RETURN

IF(actual_num >= 0, "#118DFF", "#D64550")

and Now I have non-percentage and percentage numeric values