I'm visualizing totals using the Card (New) visual in Power BI, and I’d like to add an arrow:
I can achieve this by creating a measure like this:
Sales Trend =
VAR uparrow = UNICHAR(129129)
VAR downarrow = UNICHAR(129131)
VAR sales = [total_sales]
RETURN
IF(
sales < 0,
FORMAT(ROUND(sales, 0), "#,##0") & " " & downarrow,
FORMAT(ROUND(sales, 0), "#,##0") & " " & uparrow
)
But the issue is: this turns the measure into text, which disables Power BI’s built-in scaling. So instead of showing 52 K, it shows 52,400, which doesn’t look clean when numbers go into the millions or billions.
I’ve tried two workarounds
1.Manual formatting:
Sales with Arrow =
VAR uparrow = UNICHAR(129129)
VAR downarrow = UNICHAR(129131)
VAR sales = [total_sales]
VAR sign = IF(sales < 0, downarrow, uparrow)
VAR scaled =
SWITCH(
TRUE(),
ABS(sales) >= 1000000000, FORMAT(ROUND(sales / 1000000000, 1), "#,0.0") & " B",
ABS(sales) >= 1000000, FORMAT(ROUND(sales / 1000000, 1), "#,0.0") & " Mio",
ABS(sales) >= 1000, FORMAT(ROUND(sales / 1000, 1), "#,0.0") & " K",
FORMAT(sales, "#,0")
)
RETURN scaled & " " & sign
This works visually but still returns text and loses the advantages of true numeric formatting.
2.Splitting into two measures. One for the number (still numeric), and one just for the arrow:
total_sales = SUM(fact_sales[SalesAmount])
Sales Trend Icon =
VAR uparrow = UNICHAR(129129)
VAR downarrow = UNICHAR(129131)
RETURN IF([total_sales] < 0, downarrow, uparrow)
Then I display both in the report next to each other.
But still, neither of these, look like a clean solution to me. Is there any best practice to follow?
Best practice in this case is to use dynamic format strings: https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-dynamic-format-strings