Adding nd, st etc to a number column

[jimmyfromus]

Hi, 

I've a standard number column that I'm adding to a card visual in a dashboard in powerbi. I would like however to add st to 1 and nd to 2 etc. The visual looks like this, instead of 69, I would like 69th in the card visual. 

How could I do this? Thank you.

[parry2k · Accepted solution]

@jimmyfromus your answers are so short it is wasting our time. Are you adding a new column or a measure? Why not explain everything in one go. Just a suggestions.

Since Rank is a column and if you are adding a measure, as suggested, use aggregation for Rank in this measure, for example, MOD ( SUM ( Table[Rank] ), 100 )

[parry2k · Accepted solution]

@jimmyfromus your answers are so short it is wasting our time. Are you adding a new column or a measure? Why not explain everything in one go. Just a suggestions.

Since Rank is a column and if you are adding a measure, as suggested, use aggregation for Rank in this measure, for example, MOD ( SUM ( Table[Rank] ), 100 )

[jimmyfromus · Accepted solution]

@parry2k Sorry about any confusion, I had written I had a standard number column. Your last suggestion works for me. 🙂 -> MOD ( SUM ( Table[Rank] ), 100 ). I appreciate your help. 

The solution I am now using is below where Comparison [rank] is a whole number column. 

Ordinal Suffices =

SWITCH(

TRUE()

,MOD ( SUM ( Comparison[rank] ), 100 ) IN{11,12,13} ,"th"

,MOD ( SUM ( Comparison[rank] ),10) = 1 ,"st"

,MOD( SUM ( Comparison[rank] ),10) = 2 ,"nd"

,MOD( SUM ( Comparison[rank] ),10) = 3 ,"rd"

,"th"

)

[parry2k]

@jimmyfromus rank is a measure or a column? 

Are you adding a measure or a column? If Rank is a column and you are adding a measure then you should use aggregation to use rank in your measure.

[jimmyfromus]

it's a column

[parry2k]

@jimmyfromus what is the data type of Rank column?

[jimmyfromus]

@parry2k whole number

[parry2k]

@jimmyfromus try this logic 

Number with Postfix = 
VAR __remainder = MOD ( [YourMeasure], 10 )
VAR __postFix = 
SWITCH ( 
    __remainder,
    1, "st",
    2, "nd",
    3, "rd",
    "th"
) 
RETURN
FORMAT ( [YourMeasure], "General Number" ) & __postFix

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[jimmyfromus]

@parry2k Thanks. I've tried the following but my number column doesn't seem to be picked up. What am I doing wrong here? Thanks again. 

[StefanoGrimaldi]

try out the smart narrative: https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-smart-narrative 

you can costoum made text combined with values or results.