I have a table which I am rounding to the nearest thousand using Visualisations/Field Formatting/Display units and select thousands which works fine except each number in my table has a K at the end. I would like to remove the K as it is unnecessary, any suggestions. Ideally I don’t want to duplicate all my measure’s to achieve this.
You could try creating a calculated column using SUBSTITUTE and then change the data type to be whole number.
Example:
Column = SUBSTITUTE( ‘Table’ [value], “K”, " " )
If you don’t need the full detail of the original values, perhaps you could just add one new measure (say Divisor, set to 1000) and then just edit your existing measures to use DIVIDE( [your existing measure, Divisor, 0 )…
Hi @KimC, we’ve noticed that no response has been received from you since the 14th of October. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!
While it is true that you do have to create new measures, I have a trick for you that I am using with card visuals that display aggregate measures that I have successfully tested with your situation. I usually use this technique to force a 0 to display instead of “(blank)” on cards, but it works just as well for your needs.
The important point is that you do not need to duplicate the measures, just create a new measure that divides the desired measure by 1000. This leaves with you with only one DAX statement containing your measure code so you only have to maintain the calculation in one spot. The second measure only formats the primary measure.
Here you can see the DAX I used for the formatting measure, which uses Sam’s measure branching technique. Note that I set the decimal precision to 1 even though the format is $ English which has a precision of 2 by default.
And here is the primary measure and formatting measure side by side in a table.
I put all such measures into their own measure group to make it easy to distinguish the formatting measures from the primary measures.
Perhaps overkill here, but you could set up a calculation group to dynamically substitute other measures for [Total Sales] in @jpratt’s suggested measure.
Might be worth doing however as a good learning opportunity on Calculation Groups and Tabular Editor.
Hi @KimC, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!
For the new card visuals, dividing by 1000 doesn’t seem to work for me. And there’s no way to take off the K in the Visualizations section, at least that I can see. Any ideas on how to take off the K and resolve to showing the full amount as a whole number, with no decimal?