POWER BI VISULATION VALUE BY MILLION AND QTY BY THOUSAND IN SAME REPORT(GRAPH)

[Saxon10]

Hi,

I have sales data by country with stock. I am trying to make one chart with value and qty by country. Value need to be million and stock need to be thousand in same Graph. How can I do that?

Can you please advise what is the best Graph in order to present the value and stock in same report with different measuring method (Value by million and stock by thousand)

Data:

COUNTRY	TOTAL VALUE	ALL STOCK
INDIA	9230677.53	235049
AUS	5180243.8	21351
PAK	2807674.28	456445
ENG	2291070.45	4442
SRL	1432344.89	12236
WI	1247461.67	5783
BAN	1014222.06	4978
SA	719955.07	86679
AFG	512385.58	12523
ZIM	309034.56	1569
UAE	216635.33	1151
USA	167153.55	664
CHINA	61031.63	4399
IRL	1181.35	19
SCT	664.73	204

Current visual report;

I would like to achieve

[BA_Pete]

Hi @Saxon10 ,

Select your fields in the list on the right of the report area, then add in your desired format for the number here:

Just overtype 'Decimal Number' or 'Whole Number' or whatever is there, then hit Enter.

For thousands you can use [#,##0,] for millions you can use [#,##0,,].

Then, on your chart, make sure the data label 'Display units' is None:

This will stop Power BI auto-formatting them so they revert to your desired format.

You can have a play with the different format codes so you can chage how they display if they are negative, or add 'k' or 'M' to the end etc.

This gives me the following ouput using three fields with all the same values in, just different format codes applied:

Pete

[Saxon10]

Thanks for your reply.

My raw data don't have a Total value so I make DAX function Measure Total Value = Data[StandardPrice]*Data[AllStock]

Can you please advise how can I change the format within DAX measure also I changed the thousand format for all stock but still not picking the right format in my visualization

So could you please help format for value and all stock. File attached here for your reference.

https://www.dropbox.com/s/gmxrqmtah853xxx/value.pbix?dl=0

Result Example

Total Value as a Million with two decimal (like 9.23M , 2.81M)

All Stock as a thousand with two decimal (like 456k)

[BA_Pete · Accepted solution]

Hi @Saxon10 ,

I've created two new measures:

_allStockFmt = SUM(Data[AllStock]) / 1000

_totalValueFmt = SUM(Data[Total Value]) / 1000000

I then selected each of these, changed the data format to Decimal number, and reduced decimal places to 2.

I think the following should also work if you want M and K indicators, although I've not tested in this scenario:

_allStockFmt =
FORMAT(
  SUM(Data[AllStock]) / 1000,
  "0.00K"
)

_totalValueFmt =
FORMAT(
  SUM(Data[Total Value]) / 1000000,
  "0.00M"
)

Using these in your visual I get the following output:

Just be mindful that, no matter which way you cut this, it's going to be incredibly confusing for an end user due to the fact that the axis is just a set of numbers and does not distinguish between thousands and millions. For example, in my screenshot above, ZIM allStock appears to be significantly lower than totalValue, but this grossly misrepresents the underlying values.

Pete

[Saxon10]

Good morning. Thanks for your advise and suggenstion and sorry for the late reply.

This below mentioned measure working fine. 

_allStockFmt = SUM(Data[AllStock]) / 1000

_totalValueFmt = SUM(Data[Total Value]) / 1000000

Can you please advise is there any alternative way to achieve my result.

The below mention measure not working.

_allStockFmt =
FORMAT(
  SUM(Data[AllStock]) / 1000,
  "0.00K"
)

_totalValueFmt =
FORMAT(
  SUM(Data[Total Value]) / 1000000,
  "0.00M"
)