Designating Value Types in a Measure

[nrenaud]

Hello! I am using the following measure to display data in a matrix (along with other measures). This measure displays the values I am looking for, however, some of the values are currency, some are decimal numbers, and some are a percentage. On my matrix, it only displays as decimal numbers (general).

CurrentYear Value =
SWITCH (
MAX ( 'Column/Measure Name'[Measure Name] ),
"Billings", SUM ( Table[Billings] ),
"Cash Rec", SUM ( Table[Cashrec] ),
"PL", SUM ( Table[PL] ),
"SO", SUM ( Table[SO] ))

Is there a way to display the correct format for each value in matrix? I have tried CURRENCY(value) in this measure for the lines that should be shown as such, however that does not seem to work. Is that possible to do in a measure like this?

Thanks in advance!

[dedelman_clng · Accepted solution]

Hi @nrenaud  - 

You should be able to use the FORMAT function around your values to get the desired result.  There's a good explanation on how it works here, including some examples: https://dax.guide/format/ 

Fmt Measure = SWITCH(SELECTEDVALUE(MType[MType]), 
    "Amount", FORMAT(SUM(Dates[Amount]), "$#,0.00"), 
    "Percent", FORMAT(SUM(Dates[Percent]), "Percent"))

Hope this helps

David

[TomMartens]

Hey @nrenaud , 

this is not possible out of the box.

You can use format strings in calculation groups, but be aware that calculation groups are complex feature by itself, that only can be unlocked using Tabular Editor.

Here are two links that describe what you are looking for:

• https://www.sqlbi.com/articles/controlling-format-strings-in-calculation-groups/
• https://www.kasperonbi.com/dynamic-formatstrings-with-calculation-groups/

Hopefully, this provides some ideas on how to tackle your challenge

Regards,

Tom