Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to 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
Proud to be a Datanaut!
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
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)
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
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"
)