Hi,
I have three headline texts which reside in a Multi Row Car at the top of my report:
Budget = £995k Sales = £1.1m 10% over budget
Unlike a standard Card visual, the Multi Row Card does not appear to have a Display Units = Auto option, so I have created a measure for the first two allowing me to change the numeric formatting, eg, £k for thousands and £m for anything over a million. So for Sales I have :
SalesText = "Sales = " &
SWITCH(TRUE(),
SUM(SalesFact[SalesValue])>= 1000000, FORMAT(SUM(SalesFact[SalesValue]),"£#,,.##M"),
SUM(SalesFact[SalesValue])>= 1000, FORMAT(SUM(SalesFact[SalesValue]),"£#,##0,.#k"),
SUM(SalesFact[SalesValue])= 0, "£0", FORMAT(SalesFact[SalesValue]),"#")
)
Is the only way to do this?
I would now like to further format the text, so that the Budget text is Red if negative. Is there a way to dynamically change the style of the text in either type of Card?
Thanks
Mark
Hi Mark,
You ask some great questions 🙂
Currently there's no way to apply a color format with a DAX function, but you can vote up the idea on Power BI Uservoice here: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/31560820-extend-dax-format-function-simple-but-huge-bene
The suggestions I've seen are to use conditional formatting in a table (not available in cards yet AFAIK), or use single cards with different formatting. Create two measures; one for negative results and one for positive using the IF function to hide the value if it doesn't meet the criteria. e.g.
Measure 1 =IF([Actual])<=[Budget],[Actual])<=[Budget],"")
Measure 2 =IF([Actual])>[Budget],[Actual])>[Budget],"")
Mynda
Hi Mynda,
Thanks for the tips, I did do some further reading after posting and also came to the conclusion that it wasn't possible. I think we're so used to what Excel offers and when Power BI can't do it (yet!) it comes as a bit of a disappointment, which isn't too fair considering all that Power BI can do and also how long Excel has been around!
Sort of on topic, I am really struggling to understand the DAX Custom Numeric formats. I have read the online page a few times to no avail. For instance FORMAT(-1263807.08, "£#,##0,,.##M") is displayed as -£1.26M , however I have a feeling that the code is longer than it needs to be and I wouldn't be able to explain to someone what each digit in the string is doing!
Any tips or links to clearer resources appreciated!
Thanks
Mark
Hi Mark,
Yes, good point, we must be patient with Power BI and appreciate what we have, not what we haven't 🙂
Try FORMAT(-1263807.08, "£#,,.00M")
Or if you want say, £200,000 to display as £0.20M then use:
FORMAT(-1263807.08, "£0,,.00M")
The # only displays significant digits, whereas the 0 displays both significant and insignificant digits.
Mynda
