Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Saxon10
Post Prodigy
Post Prodigy

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

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:

 

COUNTRYTOTAL VALUEALL STOCK
INDIA9230677.53235049
AUS5180243.821351
PAK2807674.28456445
ENG2291070.454442
SRL1432344.8912236
WI1247461.675783
BAN1014222.064978
SA719955.0786679
AFG512385.5812523
ZIM309034.561569
UAE216635.331151
USA167153.55664
CHINA61031.634399
IRL1181.3519
SCT664.73204

 

Current visual report;

 

Saxon10_0-1606302892778.png

I would like to achieve

 

result.PNG

1 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:

BA_Pete_0-1606314514492.png

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

4 REPLIES 4
BA_Pete
Super User
Super User

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:

BA_Pete_0-1606305328992.png

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:

BA_Pete_1-1606305449891.png

 

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:

BA_Pete_2-1606305578058.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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)

 

 

Saxon10_0-1606310503351.png

 

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:

BA_Pete_0-1606314514492.png

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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"
)

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.