mixing numeric formats in a column

[mstoler]

Hello,

I am working on an apparel dashboard where we sell shirts and socks.

For shirts we can to display number quantities.

For socks, we want to display decimals because socks are sold by the dozen and we sometimes sell 0.5 dozen which gets displayed as 1.

Looking for a clever solution for displaying this in a table.

Thank You,

Michael

[marcus_sommer]

Probably there is no solution which you would really like. One simple way would be just to display always with a digit. Another could be to add an unit field into the table and/or into the formatting and showing 0.5 dozen as 6 pieces.

If such approaches aren't wanted you could of course apply an if-loop and querying each time which type is displayed and how does the number look like to branch into different formatting (by larger data-sets it may need some extra time ...).

- Marcus

[mstoler]

Hello,

Thank you.  The if statement I feel would affect performance.   The table is already large and can take a while to load.

Maybe add a custom format string in the load script and then use the num function?

I might try that. 

Michael

[vinieme12 · Accepted solution]

for example field Category has values "Shirt", "Socks"

Then in chart

Dimension

Category

Measure

=Pick(Wildmatch(Category,'Shirt','Socks')

,num(sum(Qty),'#,##0')

,num(sum(Qty),'#,##0.0')

)  << choose formatting as Measure Expression

[mstoler]

Hello,

I am going to extend this idea by storing the format at the item level and then using the only function to get the format into num.

Then use Dimensionality()=0 to show the totals with no decimals. 

Thanks!

Michael

[mstoler]

Why is it that when you export to excel you get the 'Number stored as text'?

Michael