var __value = [Total Profit] var __abs_value = ABS(__value) var __format = SWITCH( TRUE(), __abs_value >= 1000000, FORMAT(__abs_value, "###,,.0M"), __abs_value >= 1000, FORMAT(__abs_value, "###,.0K"), __abs_value >= 0, FORMAT(__abs_value, "##0") ) RETURN """" & __format & """" Above is the DAX I used to dynamically format the Total Profit measure. I would like to display Total Profit and Total Sales measures for Products in a Clustered column chart. A minus sign should preceed the negative values of Total Profit, thereby indicating losses. The Total Profit displays negative values with a minus symbol when they are visualized in a Line chart, but not when displayed in the clustered bar chart. Can you explain how to display the Total Profit measure that is dynamically formattef in a clustered column chart with minus sign for negative values?
The Y-axis problem from minute 10:55 - Bas from "How to Power BI" channel has a workaround for that - you create a dummy measure with irrelevant value (such as 0), put it in front of your measure and hide all of it's lines labels and markers. If you want to delete it from the tooltip too, you can create a custom tooltip. Other than that, this is a good video, you are a competent instructor and you have a great production value, video editing, sound etc. Good luck.
@@calvincastagnet7314 the video is called "100% Control of Data Labels in Power BI" posted on April 12, 2022. The part regarding this problem is between 06:17-08:30, but you may need to watch it from the top for additional context.
Hi Injae, great tutorial. Is it possible to upload the pbix? I'm using Power Toys OCR, with the measures being so long, but its not vey accurate. Thanks
Hi CT, I'm honestly just thinking about setting up a Patreon or something so that people who subscribe for like 5 bucks can just get all my files... But I never got around to it 😅
Hi, I usually use the accompanying dataset from PowerBI so everyone can use it quickly :) You can see how I get it from power bi desktop directly here: ruclips.net/video/_NEnQNp102U/видео.html
Hi Edwin, thanks! I'm trying to set up a way to publish all my reports - looking into starting my own website or something 😊 All my PBIX will be released soon :D
Hi Aneta,. it's a good question, and I don't think I explain it very clearly. So first off, the whole output needs to be shown as text, so it's wrapped quotation marks like ["text"] However, we need to the output to think that the whole output includes quotations, even in text form, so to do this, you need double quotes - [""] = " And then because the whole thing now needs to also be converted to text once again for the dynamic format string to work, it needs another 2 quotes, resulting in 4 quotes 😂 It's honestly very strange, but it won't work without all 4 (。_。)
@@anetakn6601 because the quotes denote an enclosed text, the & sign essentially acts to add two bits of text together, like the concat function in powerbi or excel ☺
@@PowerBIPark I still don't get it. Like for instance when I wanted to add the measure as a Subtitle in a card visual, I just recreated the same measure, but this time only used "". Maybe I could understand it if you could explain the difference between the two. Also why use "#,#0" instead of "##0"?
Hi Jim, almost missed this comment So the main thing to understand is that dynamic format strings basically are an extension of the dax function FORMAT(), where the argument needs to be a text. So if you wanted to just input these calculations into a subtitle, you need to remove the extra "", since that converts the outputt of the format string into a text. the [,] used acts as a divider for thousands - without it it would return a value like 100000000000 instead of 100,000,000,000
Hi, I usually use the accompanying dataset from PowerBI so everyone can use it quickly :) You can see how I get it from power bi desktop directly here: ruclips.net/video/_NEnQNp102U/видео.html
var __value = [Total Profit]
var __abs_value = ABS(__value)
var __format =
SWITCH(
TRUE(),
__abs_value >= 1000000, FORMAT(__abs_value, "###,,.0M"),
__abs_value >= 1000, FORMAT(__abs_value, "###,.0K"),
__abs_value >= 0, FORMAT(__abs_value, "##0")
)
RETURN
"""" & __format & """"
Above is the DAX I used to dynamically format the Total Profit measure.
I would like to display Total Profit and Total Sales measures for Products in a Clustered column chart.
A minus sign should preceed the negative values of Total Profit, thereby indicating losses.
The Total Profit displays negative values with a minus symbol when they are visualized in a Line chart, but not when displayed in the clustered bar chart.
Can you explain how to display the Total Profit measure that is dynamically formattef in a clustered column chart with minus sign for negative values?
The Y-axis problem from minute 10:55 - Bas from "How to Power BI" channel has a workaround for that - you create a dummy measure with irrelevant value (such as 0), put it in front of your measure and hide all of it's lines labels and markers. If you want to delete it from the tooltip too, you can create a custom tooltip.
Other than that, this is a good video, you are a competent instructor and you have a great production value, video editing, sound etc.
Good luck.
Thanks for the tip! Bas always has the answer, I'll definitely have to check it out :)
I confirm that it work
@zis300 Is there a link for BAS's Video? Thanks
@@calvincastagnet7314 the video is called "100% Control of Data Labels in Power BI" posted on April 12, 2022. The part regarding this problem is between 06:17-08:30, but you may need to watch it from the top for additional context.
Hi Injae, great tutorial. Is it possible to upload the pbix? I'm using Power Toys OCR, with the measures being so long, but its not vey accurate. Thanks
Hi CT, I'm honestly just thinking about setting up a Patreon or something so that people who subscribe for like 5 bucks can just get all my files... But I never got around to it 😅
is there a reason you used sameperiodlastyear vs dateadd for the LY selected axis? Min 17:25
Not particularly, I just wanted to try it out. I'm trying to use more offset these days for time intelligence now 😁
I also have offsets on my calendar table, just still need to practice applying them. @@PowerBIPark
Injae, do you have a sample of using Offsets for week over week?
I don't have one, but I've got an awesome video about offset to check out:
ruclips.net/video/r09wlvwMh50/видео.html&ab_channel=EnterpriseDNA
@@Gerschk
Hi, Thanks for the video, it was very helpful. I have a question. In power bi, how to use dynamic format in horizontal bar chart
Please provide the dataset and file so i can practice on it thanks
Hi, I usually use the accompanying dataset from PowerBI so everyone can use it quickly :)
You can see how I get it from power bi desktop directly here: ruclips.net/video/_NEnQNp102U/видео.html
Nice video can I find the pbix file, thanks
Hi Sohail, I haven't made a way to share the pbix files yet, but will come soon :)
Hi, great stuff. How did you add that blue arrow up to the chart? Windows key and then ...... ??
Hi Ron, it's the windows key and [;] 😁
On the grind I see 💪 Keep up the banger work! (also when will you be on again)
Yeah ik about done with tft, so soon 😁
El formato dinámico no me sale en matrices, es solo aplicado a tablas y otros visuales?
It should work - as long as you have not overwritten the display units within the matrix format
Thanks once again
great video - thanks!
So cool, can you share the pbix?❤
Hi Edwin, thanks! I'm trying to set up a way to publish all my reports - looking into starting my own website or something 😊 All my PBIX will be released soon :D
Amazing video
Absolutely useful!!
Maybe a stupid question.
Can you explain this " " " " & and & " " " " part of the code? Line 10 and 12 in the Level 4 code.
I don't get it.
Hi Aneta,. it's a good question, and I don't think I explain it very clearly.
So first off, the whole output needs to be shown as text, so it's wrapped quotation marks like ["text"]
However, we need to the output to think that the whole output includes quotations, even in text form, so to do this, you need double quotes - [""] = "
And then because the whole thing now needs to also be converted to text once again for the dynamic format string to work, it needs another 2 quotes, resulting in 4 quotes 😂
It's honestly very strange, but it won't work without all 4 (。_。)
@@PowerBIPark thank you. And what about & ?
@@anetakn6601 because the quotes denote an enclosed text, the & sign essentially acts to add two bits of text together, like the concat function in powerbi or excel ☺
@@PowerBIPark I still don't get it. Like for instance when I wanted to add the measure as a Subtitle in a card visual, I just recreated the same measure, but this time only used "". Maybe I could understand it if you could explain the difference between the two.
Also why use "#,#0" instead of "##0"?
Hi Jim, almost missed this comment
So the main thing to understand is that dynamic format strings basically are an extension of the dax function FORMAT(), where the argument needs to be a text. So if you wanted to just input these calculations into a subtitle, you need to remove the extra "", since that converts the outputt of the format string into a text.
the [,] used acts as a divider for thousands - without it it would return a value like 100000000000 instead of 100,000,000,000
Great stuff 🙏
please provide data set :) thanks
Hi, I usually use the accompanying dataset from PowerBI so everyone can use it quickly :)
You can see how I get it from power bi desktop directly here: ruclips.net/video/_NEnQNp102U/видео.html
👍