COMBINE CLUSTERED AND STACKED COLUMN CHART/BAR CHART INTO ONE VISUAL WITH LINE VALUES IN POWER BI
ฝัง
- เผยแพร่เมื่อ 6 ก.ย. 2024
- In This Video -
- How do you combine stacked and clustered charts in Power BI?
- How do I combine clustered and stacked bar charts?
- How do I put a clustered column next to a stacked column?
Get Help into Questions Related to #powerbi , #dax , #powerquery Power BI #datamodeling , #analysisservices and #powerbiservice
Our videos are mostly specific to real time challenges faced into Power BI projects and its solutions. We use smart and most efficient techniques to solve such challenging situations in Power BI and DAX.
Checkout our playlist and SUBSCRIBE to our channel, for all such interesting topics in Power BI.
Check out our website www.powerbihelpline.com and blog-post. for end-to-end power BI solutions, Power BI templates, or any kind of Power BI questions like Power query errors, Authoring and optimizing DAX codes for complex business logic. #powerbisolutions #powerbitutorials #powerbitraining #powerbidashboard #powerbideveloper #powerbipro
Follow us on LinkedIn
/ power-bi-helpline-22a2...
Email- info@pbihelpline.com
@POWERBIHELPLINE
This video was so helpful for my Internal Audit Department. It helped us compare the budget vs actual hours per phase (planning, fieldwork, reporting and wrap up) in each of the different engagements. I struggled a lot to create the chart before finding this video. Much thanks!
Glad to know you found this video helpful. this idea came from exact similar requirements, which i got from one of the projects. and i knew that, it will be very helpful to many other people. Thank you so much. 🙂
Great help, thank-you. I changed mine to a Stacked bar chart and I found that I can leave out the blank column, and just use the 'Horizontal' Gridlines, as a separator. Looks better than having the gap.
Glad it helped!
Nice video but how do we create relationship between NEW AXIS and other tables so that we can calendar and new axis on X axis?
That New Axis is dependent on measures, based on what is available in the context. No relationship needed with other table, all your measures depending on date or any other table will work perfectly fine. As it is working for Country in the demo.
Can someone please advise how to fix this issue as I have date + axis on x axis. How do we establish relationship with axis table?
Worked perfectly, thanks a ton
Finished watching
Thank you so much
Thank you very much for this tutorial. It works! May God bless you
You're welcome! Thanks for the kind words and wishes
As the X axis has two columns from unrelated tables I face the error Can't determine relationships between two fields and the visual is not shown. I have read in documentation that its not possible to change it. Could You please advice the direction? I don't have a sample to send.
If your measures are able to identify the current context of evaluation, it should calculate the value for the current X axis value. as you said, you have unrelated tables, it needs to be investigated with a sample PBIX file.
Was very helpful for me, thanks for the detailed explanation.
I do have one question: is there any way to sort the columns by forecast amount?
Forecast here is a separate column for each category which is clustered with the Actual Sales amount stacked column. Now if you want to sort by forecast, but still want to see the Category wise Sales and forecast, then you need to create Ranking based on a separate measure.
Thanks for the video, very useful. Although I cannot make the chart drill-able up and down with the arrows, in case I wanted to add another aspect to the X axis, for me it can only jump to the lowest hiearchy drill. How do you make the created chart drill-able up-down, if you wanted to add additionally to Country also e.g. Segment to the X axis? Thanks!
Glad it helped!
When we create such custom axis, The DAX expression which we author to enable the functionality is the key, and this expression will change based on varying requirements. in your case, where you need another details with drill down, so we need to change the DAX expressions accordingly.
Thanks, can you pls give me a hint how to change this dax or which other one to use for the drill down to work? Thanks
"It's so grateful. I also tried to use line charts including the above, but I couldn't see the curve on the chart. Could you please help me with this? How can I add curves here?"
That line curve is nothing but the different values for each category on the x axis. Please make sure that you have a correct DAX measure for the same.
Could you please share how to do measure for the line-y curve, when I add line, it shows multiple lines for each X-axis, it's not one single line as expected. Thanks!@@PowerBIHelpline
Really Great video. This used for real time scenarios
Glad you liked it
Awesome! Thanks for the video! its really helpful!
Is there a way to remove those two x axis label(sales and forecast) just to keep only country? Also, data label is not visible for less stacked bar. Is there any option to show case that too? Appreciate your help!
Yes we can do all these customization.
Very helpful! However, I am able to use this trick to create the complete vizual except when I make the spacing 0, it also removes the space between the categories (country in your example and month in my data). Any suggestions?
Another way is remove the 0, and leave it only with Actual and Budget, then adjust the spacing between columns from "Spacing" option. Use a vertical grid line in the axis to separate the main category. you can send an email with video subject to get the PBIX file, where i have also created this formatting as well.
Nice one... Thanks for sharing
I just want to know what is the best way to show Comparision between Previous Year and current Year......
I am glad, you found this video helpful.
There are multiple ways to compare previous year vs current year
checkout below amazing video related to this -
SHOW PREVIOUS YEAR AND FORECAST IN SAME COLUMN CHART/BAR CHART IN POWER BI | NO CUSTOM VISUAL
th-cam.com/video/fpStz1BuApo/w-d-xo.html
is it possible to create like Total Sales as Background Transparent and front shows split by legend. Is it possible in Power BI? Excel we can Create
This is a good idea. We would like to post a video on this
This is simply brilliant - works like a charm!!!
Glad to know this is so useful.
Thank you for sharing this video, i have an issue with the Blank measure, when i drag the measure to Y-axis, my x axis gets wider as i have three categories /columns( clientname, year & month) on x axis. how can i show only the results for slicer I selected on the page?
Hi, your query is not clear. Blank measure is just used to have a fixed gap between categories.
Please send a sample PBIX file to info@pbihelpline.com with requirements details
How would you do this if your x-axis is using a master calendar which you use to drill down from year, quarter, month, day?
Thanks for posting your valuable comment. you can use date hierarchy on X axis, but you need to see if you have the lowest granular data for budget. Mostly Budget data is not available on daily levels, so use it accordingly.
This video is incredibly helpful and powerful!
Thank you so much for the feedback. I always try to post videos on such topics, which i feel could be very useful for Power BI Professionals.
excellent, another helpful video, kudos to you 👏
How would you get the country name to be centered underneath the Sales/Forecast columns?
That is actually centered, but as you see we have 3 categories for each country including a blank space. this third category with background color is required to keep blank space between countries.
How to use multiple x axis measures [here only country is there] to drill down on each with this functionality of 2/3 stacked and one clustered column chart
Combining Stacked column chart with Clustered column chart is not by default available in Power BI, and it can be achieved with such workarounds itself. if you want to have further drill downs with multi category x axis, the solution will become more complex.
Hello, Thanks for your video, I have done all the steps correctly, but I am getting this error message and cant display the visual , please find the message here " MaxScript(Model) (53, 5) Calculation error in measure Raw R Total External Invoices): DAX comparison operations do not support comparing values of type Integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values " , Can you please help me in resolving this issue ? Thanks in advance!
This error message already has the reasons, to resolve the issue, we need to look into your DAX expressions and data types of the columns used.
Genius. But I still have a lottle visualization problem : on the x-axis I put dates and categories. In the visual I correctly see the dates but I don't want to see anything else. How to do it? Thanks a lot
I changed "sales" and "forecast" to "." and ".." and then used a white bar to highlight them out
I actually removed the white bar, the dots are small enough to not get in the way and kind of help to show which column is first and second (I removed the white column as well)
@@michaelungar241 😂 I just did the same... I tried before with a blank space but i did't work
My forecast value is empty(Can't figure what's wrong.
It shouldn't be. Make sure you have correct forecast measure , and/or not missing any step.
i follow your video but can not create chart like this. I get error 'there is a missing relationship between these fields' :(
It may be because of your data model has been setup differently. if you want to look into the PBIX file used in this demo. you can send an email to info@pbihelpline.com which will help you more in implementing the same into your reports.
I am not able to put more than one measure in Y-Axis
Please make sure you are not missing any step shown in the video. and if you don't have any different issue in your data model, it should work perfectly fine.
Thanks for the video. After many attempt unfortunately I didn't have the same result 😭
May be you are missing something, if you would like to have a look into the pbix file used in the demo, you can send an email by marking video title as email subject.
That‘s great!Could i get your PBIX?
Thanks for watching. To get the PBIX file, you can send an email with video title as email subject.
Is it ok if after I finish the chart, I remove the blank column?
That blank space is used just to keep a fixed gap between category, otherwise that is not required, but if you will remove that, you will see a continuous columns without gaps.
@@PowerBIHelpline I love the overall technique, but prefer to remove the blank column so it's more centred and then add spacing (inner padding) between the columns. As long as vertical gridlines are switched-on (and maybe set a little darker than the default), it is visually obvious which child categories (sales/forecast) are paired together under each parent category (country).
@@simonbeck8948 This is line and stacked column chart, but you are also experiencing the clustered columns for budgets with the tricks.
now, the problem is you can not have inner padding, if you do so, you will loose the clustered budget column. you try that, and you will find why there is a blank used for this with matching background color. but if you wish to utilize the vertical grid lines for separator, then obviously, we can remove the blank, and keep an equal inner padding
How do I do if I'm using Direct Query ?
It will work same way. You can create other tables even if you have a direct query connections for one of your table
This is not working when i want the x axis to be calendar month and year
This particular technique is to show, how to combine these features in a single column chart. It should work with any dimension, however there are some changes required for each custom requirement.
mine just keeps saying "can't display the visual" I for sure followed the steps to a T, and this is the only video on this.
Thanks for posting your query. It may be due to some existing relationships in your data model, which are not expected to be there for this entire setup.
if possible you can send a sample PBIX file to info@pbihelpline.com to check further
@@PowerBIHelpline So I got it working now, however I am unable to drag the blank measure into the x-axis field.