Data Scale

[rfreuden25]

I am loading data into power BI through excel.  My Data is in thousands.  When I model them into chart's graphs etc it treats them as real numbers.  What is the most efficient way to close this gap?

[v-piga-msft]

Hi @rfreuden25,

I'm a little about your scenario, could you share some data or screenshots to describe your problems in more details?

Best Regards,

Cherry

[rfreuden25]

For example:  Revenues of "123.456" in my data set translates to $123,456.00.   When I put that revenue number into say a bar chart, with the thousands scale, it shows as .123456 when in fact i want it to be 123.456.

[v-piga-msft]

Hi @rfreuden25,

You could set the data Display Units under format like below.

If that still doesn't work, you could create a calcualted column to convert the 123456 to 123.456.

Column = 'Table'[Revenue]/1000

If you still need help, please share a dummy pbix file which can reproduce the issue, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

Best Regards,

Cherry

[rfreuden25]

The Auto function doesn't account for my data being in thousands already.  While a calculated column would fix the issue I have around 100 Columns per table and about 5 tables so far.  I feel that would potentially lead to errors and a lot of ram being used. 

There is a function(Standard) at the top of query editor under the transform tab that will allow you to multiply columns, but it will not let me do multiple columns at a time and I don't want to manually adjust 500+ columns of data. 

We need a setting in the query editor that will allow us to specify whether our data is in real numbers, thousands, or millions etc.

[Terp]

---

@rfreuden25 wrote:

We need a setting in the query editor that will allow us to specify whether our data is in real numbers, thousands, or millions etc.

---

Exactly. I feel your pain. There must be a more elegant 'scale data' solution than to 1.) create double the number of fields to do a quick calcs for every single field when the data are expressed in thousands, millions, billions, etc, or 2.) create a custom value like in excel to add the million or billion to the raw number (e.g., $0.0,,"B").

Sure, the later 'sort of' works for me, but when when the numbers decrease/increase appreciably, well, you run into the "$0.0T" issue.

So frustrating for something seemingly so common.

Did you every figure out a workaround?  I was going to start yet another thread on this topic in hopes of figuring out how to address it properly. 

[rfreuden25 · Accepted solution]

Maybe the easiest solution at this point is just to multiple my measures by 1000.