Help needed with a visual in Power BI - stacked bar and line chart with date in the x-axis

[LimpingWolf68]

Hello, I'm new in Power BI and have been struggling for weeks now with a visual in which I want to show hours realized and scheduled in specific months. I have a table in which I have this information. So with this information I can build a stacked bar chart with realized and planned hours in the months. I can drill to the days. I have a filter which filters on groups of employees. So if I have the filter on 'all' I see the result of all employees for all months in a year.

I want to add a line to the visual and am using the 'line and stacked column chart'. The line represents the target hours a group of employees needs to realize in a certain month or day. So together with the columns it shows the target (line) and the result (columns) in one chart. The information for the line is in a different table. This table has a date column and a column which refers to the group of employees. I have managed to connect the two tables using a third table for the employees. I also created a date tabble, but as soon as I connect the tables using this date table, I loose my drill functionality (so no view per month anymore) and the totals of the targets seem to add up. Each month shows the total for the whole year.

I'm doing something wrong, but I just don't know what. It's something in the table I use for the line I guess. Anyone got a suggestion?

[123abc · Accepted solution]

It seems like you're encountering some common challenges with Power BI visuals, particularly when trying to integrate different data sources and maintain drill-down functionality. Let's address these step by step:

1. Drill-down Functionality Issue: When you connect your tables using a date table, you might be losing the drill-down functionality because the relationships between tables are not set up correctly. Make sure you have established relationships between your date table and other relevant tables (e.g., your main data table and the table containing target hours). Ensure that these relationships are set up correctly to support drill-down by month.

2. Total Issue: When you connect tables, and your totals seem to be aggregating over the whole year instead of by month, it's likely because your date table is not being used properly to segment the data. Double-check the relationships between your tables and the filters applied to ensure that your data is being aggregated correctly by month.

3. Integration of Target Hours: Since your target hours data is in a separate table, you need to ensure that it's integrated correctly into your visual. Make sure you have a relationship established between your main data table (containing realized and planned hours) and the target hours table. Also, verify that the granularity of the date in the target hours table matches the granularity of your date table to avoid aggregation issues.

4. Visual Setup: Once your data relationships are correctly configured, you can set up your visual. Use the 'line and stacked column chart' visual and assign the date field to the x-axis. Assign the realized and planned hours to the stacked columns and the target hours to the line.

5. Filters: Ensure that any filters applied to your visual (such as filters on employee groups) are correctly set up to apply to both the main data and the target hours data.

By carefully checking these points and adjusting your Power BI setup accordingly, you should be able to create the desired visual with both the stacked bar and line chart, while maintaining drill-down functionality and accurate aggregation of data by month. If you encounter specific issues or error messages during this process, feel free to provide more details, and I can assist you further.

[LimpingWolf68 · Accepted solution]

I have managed to solved the problem. The issue was a self created date table and the type of the date columns. I used an existing example and made sure I set the type of the column right and that did the job 🙂
Many thanks again

[LimpingWolf68]

Many thanks 🙂 

I'll try to solve my problem and in case I need any further help, I'll get back to you

[123abc · Accepted solution]

It seems like you're encountering some common challenges with Power BI visuals, particularly when trying to integrate different data sources and maintain drill-down functionality. Let's address these step by step:

1. Drill-down Functionality Issue: When you connect your tables using a date table, you might be losing the drill-down functionality because the relationships between tables are not set up correctly. Make sure you have established relationships between your date table and other relevant tables (e.g., your main data table and the table containing target hours). Ensure that these relationships are set up correctly to support drill-down by month.

2. Total Issue: When you connect tables, and your totals seem to be aggregating over the whole year instead of by month, it's likely because your date table is not being used properly to segment the data. Double-check the relationships between your tables and the filters applied to ensure that your data is being aggregated correctly by month.

3. Integration of Target Hours: Since your target hours data is in a separate table, you need to ensure that it's integrated correctly into your visual. Make sure you have a relationship established between your main data table (containing realized and planned hours) and the target hours table. Also, verify that the granularity of the date in the target hours table matches the granularity of your date table to avoid aggregation issues.

4. Visual Setup: Once your data relationships are correctly configured, you can set up your visual. Use the 'line and stacked column chart' visual and assign the date field to the x-axis. Assign the realized and planned hours to the stacked columns and the target hours to the line.

5. Filters: Ensure that any filters applied to your visual (such as filters on employee groups) are correctly set up to apply to both the main data and the target hours data.

By carefully checking these points and adjusting your Power BI setup accordingly, you should be able to create the desired visual with both the stacked bar and line chart, while maintaining drill-down functionality and accurate aggregation of data by month. If you encounter specific issues or error messages during this process, feel free to provide more details, and I can assist you further.