Converting only chosen rows to %

[DaxTrader]

Hi,

using PowerBi for work. New to this but learning by doing!

I import different excel tables via PowerQuery and update the data source every month with new data. 

Question: I got a table where I have to format/convert only the last three rows to %. 

Sorry, for it being in German. But the last three rows should be displayed like this 0,02 --> 2%. The values are in decimal and converting the whole column clearly doesnt help.

Thanks in advance! 

[v-hjannapu]

Hi @DaxTrader,

Thank you  for reaching out to the Microsoft fabric community forum.
I would also take a moment to thank  @ryan_mayu , @CPCARDOSO , @Greg_Deckler , @rajendraongole1 for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

In Power BI, column formatting works for the whole column, so there is no direct option to format only a few rows differently. The workaround is to create a measure and use a DAX expression with SWITCH(). This way, if the row is one of those three KPIs, it will show as percentage, and for the rest it will stay as numbers.

So with this approach, values like 0.02 will display as 2%, while the other values remain unchanged

I tested it with  sample data, and it worked fine. Please find the attached Pbix for your reference.
Hope this helps if you have any queries we are  happy to assist you further.
Best Regards,
Harshitha.

[v-hjannapu]

Hi @DaxTrader,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

Regards,
Harshitha.

[v-hjannapu]

Hi @DaxTrader,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We are always here to support you.


Regards,
Harshitha.

[v-hjannapu]

Hi @DaxTrader,

I wanted to follow up and see if you have had a chance to review the information that was shared. If you have any additional questions or need further clarification, please don’t hesitate to reach out. I am here to assist with any concerns you might have.

Regards,
Harshitha.

[ryan_mayu]

@DaxTrader 

if this column is a measure, you can try dynamic format

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-dynamic-format-strings?wt.mc_id=DP...

[CPCARDOSO]

Thanks for your patience! Since I don’t have access to your actual Excel file, I couldn’t run the transformation — but no worries, I’ll walk you through it like a proper data-savvy mate.... You’ve got an Excel table in Power BI, and every month you update it with fresh data. Lovely. But now you’ve got a cheeky little challenge:

You want to convert only the last three rows of a column (which contains decimal values like 0.02) into percentages (like 2%), without messing up the rest of the column.

Sounds simple, right? But Power BI doesn’t let you format just a few rows in a column — it’s all or nothing. So we need a clever workaround.

 

The Cheeky Solution (Power Query Style)

Here’s how you can do it using Power Query (a.k.a. the engine room of Power BI):

Step 1: Open Power Query

• In Power BI Desktop, go to Transform Data.
• Select the table you’re working with.

Step 2: Add an Index Column

This helps us identify the last three rows.

• Go to Add Column → Index Column → Start from 0.

Step 3: Find the Last Three Rows

Let’s say your table has 100 rows. The last three will be rows 97, 98, and 99 (index 97 to 99).

• Go to Add Column → Custom Column.
• Use a formula like this: if [Index] >= Table.RowCount(#"Previous Step") - 3 then [YourColumn] * 100 else [YourColumn]

Replace YourColumn with the actual name of your column.

Step 4: Format as Text with a %

If you want it to look like 2%, update the formula: if [Index] >= Table.RowCount(#"Previous Step") - 3 then Text.From([YourColumn] * 100) & "%" else Text.From([YourColumn])

Boom! Now only the last three rows show percentages, and the rest stay as they are.

---

Final Tip

Power BI doesn’t like mixing formats in the same column (numbers and text), so this trick works best if you’re just displaying the values, not doing maths with them later. If you need to calculate stuff, keep the original column and create a new one just for display.

---

Fancy Giving Me a Kudos?

If this little trick saved you from banging your head against the desk, I’d be well chuffed if you gave it a Kudos on the Microsoft forums. It helps others find the fix and makes me look like I know what I’m doing. Cheers, legend! 🍻

[Greg_Deckler]

@DaxTrader To prevent returning text, use the basics of @rajendraongole1 approach as a custom format string for your measure.

[rajendraongole1]

Hi @DaxTrader -  you can create a measure as below:

DisplayValue (Measure) =

VAR val = SELECTEDVALUE('KPI_Table'[Portfolio])

VAR kpiv = SELECTEDVALUE('KPI_Table'[Charakteristika])

RETURN

    SWITCH(

        TRUE(),

        kpiv IN {

            "Verschuldung (LF Schulden vs Market Cap)",

            "Gewinnwachstum 5 Jahre - Erwartet",

            "Dividendenrendite"

        }, FORMAT(val, "0.00%"),

        FORMAT(val, "0.00")

    )

Hope this helps.