Programmed Decimal Formatting

[shaunwilks]

I have a scenario I cannot get around.

Requirement:

The number of decimal places displayed in a QTY field will be determined by a value in another query table. And I want the field to remain as a decimal data type.  I dont want to rely on the "Modelling" tab for Data Type and Format. Auto just doesnt work, and I cannot fix the number of decimals. 

Background:

The report will be used by numerous customers, all which may have a different requirement for the display of Qty.

Some may want qty to display 2 decimals, some 4 some 6. The number of decimal places they want is stored in a table.

Testing:

Now I can get this working (Using the below measures) but this leaves the field formatted as a Text - But I dont want it as a text format as it can be used in other arithetic functions and formulas and right aligning it everywhere in Visuals is really annoying

QtyFormat = 
SWITCH('Settings'[Decimals_Qty])),
1, "0.0",
2, "0.00",
3, "0.000",
4, "0.0000",
5, "0.00000",
6, "0.000000"
)

Quantity = FORMAT(CALCULATE(sum('Invoices'[Qty])),[QtyFormat])

Other options considered:

When I use the VALUE function to change the field back to a Decimal - it goes back to the Modelling tab for its display properties and number of decimals to display.  The FIXED dax function also creates a text object at the end.

So then I thought I would use M script in the Query Editor and advanced script using the "Round" function.  But rounding a whole number e.g 23 to 6 decimals doesnt change the display of that field to 23.000000 as I would want it to. The display of the field is still handled via the Modelling tab.

Has anyone got any other ideas or am I going ot have to live with all my decimals fields in my project being text ?

[v-jiascu-msft]

Hi @shaunwilks,

How about showing them as TEXT in the visual while using VALUE to convert to numbers in a measure? I would suggest you vote up this idea.

Best Regards,

[shaunwilks]

I have tried that but its really tough cause the tooltip on the visual shows different number of decimals to the measure - if you get my drift. 

I just think the FORMAT function is very very limited if you are using it for a decimal.

Has next to no function really if its to be used in the majority of visual types.

Anyone else got any suggestions ?? 

I urge anyone reading this to vote up the idea in the link provided it (I added it today after spending large amounts of time looking for a workaround)