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
GhadaMehrez
Helper III
Helper III

Convert field into percentage

I need to convert field value into percentage but when I choose to show value as a percentage I got all values as 100% which is not right:

GhadaMehrez_0-1658684122893.png

 

15 REPLIES 15
v-rzhou-msft
Community Support
Community Support

Hi @GhadaMehrez ,

 

As far as I know , Power BI will convert the data format based on column. According to your screenshot, I think there are two columns in your visual. One column with text data like "Target Guidance(%)", another column with number format data. So if you change the data format as percentage in another column , all data in that column will show as percentage.  

I think you can try FORMAT() function and SWITCH() to create a measure. Please note that your measure will be in text format due to Power BI doesn't support multiple data format in one column or measure.

 

Measure = 
SWITCH(
MAX('Table'[Column1]),
"Target Guidance(%)",
FORMAT(COUNT('Table'[Column2]),"#%"),
"Acutal Booked Cost(DKK)",
COUNT('Table'[Column2])
)

 

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

jennratten
Super User
Super User

It is best to create a measure and format the measure to the intended type. The screenshot shows you are trying to count the number of values.  Please show an example of your data in the visual and explain what the expected out come should be.

As you see here I have fields related to estimated and booked cost in DKK I want for the up 2 rows to show the numbers in percentage 

 

GhadaMehrez_0-1658690031420.png

 

You can open the Power Query editor, go to the column with the values and change the type to percent.

I guess I don't have access to change in columns 

Question: what if I changed the type of the column and I want to use the cost in DKK not only percentage 

 

GhadaMehrez_0-1658690918095.png

 

Okay, if you can't transform the data in Power Query then you should create a measure and then assign the data type to the measure.  Then replace the column value in the visual with the measure.  Create additional measures for any other calculations and add those to your visuals as well.

In #3 below, change the aggregation function to one that is relevant for your scenario.

jennratten_0-1658696883588.png

 

Thanks a lot I followed the steps for the measure but for the aggregation function I

put Value I don't know if it the right one I just select Estimated cost field and filtered by project and created the measure but I still have the wrong percentage, any advice?

 

%ETC = VALUE( (CustomCulculations[Actuals Booked]))

 

Please try these:

This one for the percent value

% ETC = count(CustomCulculations[Actuals Booked])

This one for the number value

# ETC = count(CustomCulculations[Actuals Booked])

 

something is wrong here:

GhadaMehrez_0-1658922172933.png

 

Try this one instead:  count(Portfolios[pum_Name])

???? this not the intended feild (actuals Booked) to convert currency into %

Okay, I took that from your screenshot.  The table and column name need to appear inside the parenthesis.  Also, since it is currency you are wanting to convert, I recommend you do not use count as the aggregation function.  Without seeing your data and model it's difficult to say which aggregation function will be best, but you can try using sum instead of count, so sum(TableName[ColumnName]).  Be sure to replace the table name and column name with the actual values from your model.

sum aggregation not works 

What is the result vs. the expected result?  Can you add some snips of your data and model? 

I hope this help:

 

% Actuals Booked = VALUE(CustomCulculations[Actuals Booked])
% ETC = VALUE(CustomCulculations[Estimated Total Cost])

GhadaMehrez_0-1658928315401.png

 

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.