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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
PowerNoob
Frequent Visitor

Help with stacked columns with different x values in same data entry

I have a datset where the entries are by model / serial number. Each entry can have phase 1 and phase 2 builds with associated hours. I'm looking to create a stacked column display for model/serial build time by work week separating colors by the phase of the build. The first image below shows the scheduled time graph I'm building.

 

The problem I'm running into is that the graph is displaying everything summarized by the phase 1 schedule week. In other words Phase 2 hours are stacking on Phase 1 scheduled work week. How can I get phase 2 hours to show on the correct week? The second graph shows a selection where week 29 is selected, but includes also phase 2 hours for the build where phase 1 is schedule in week 29 (phase 2 is selected). How can i get the 135 hours to show in week 35?

 

I've tried a number of different sorts, and even separating the build hours into a separate table. I can't seem to separate phase 2 hours. 

 

Thanks in advance!

 

ExampleGraph.JPG0adcde75-cd98-4d49-896a-0cfc77c7b747.jpg

DataSelectionGraph.JPG

1 ACCEPTED SOLUTION
PowerNoob
Frequent Visitor

I solved this by:

  1. Creating a calender table based on week number with this DAX: 
  2. Week Num Base = VAR DateStart = MINX(tblSchedule,[P1Date].[Date]) VAR DateEnd = MAXX(tblSchedule,[ShipDate].[Date]) VAR simpledatetablesonlysunday = FILTER ( ADDCOLUMNS ( CALENDAR ( DateStart, DateEnd ), "weeknum", WEEKNUM ( [Date], 1 ) ), WEEKDAY ( [Date] ) = 1 ) RETURN simpledatetablesonlysunday
    2. Setting up a query that pulls P1 data and relating with a date key for year/work week in its own table
    3. setting up a separate query that pulls P2 data and relating with a date key for year/work week in its own table
    4. relating P1, P2, and work week tables by the date key
    5. graphing on a Line and Stacked Column Chart with calendar week number on X-axis, and P1 and P2 data on Y axis. 

 

View solution in original post

1 REPLY 1
PowerNoob
Frequent Visitor

I solved this by:

  1. Creating a calender table based on week number with this DAX: 
  2. Week Num Base = VAR DateStart = MINX(tblSchedule,[P1Date].[Date]) VAR DateEnd = MAXX(tblSchedule,[ShipDate].[Date]) VAR simpledatetablesonlysunday = FILTER ( ADDCOLUMNS ( CALENDAR ( DateStart, DateEnd ), "weeknum", WEEKNUM ( [Date], 1 ) ), WEEKDAY ( [Date] ) = 1 ) RETURN simpledatetablesonlysunday
    2. Setting up a query that pulls P1 data and relating with a date key for year/work week in its own table
    3. setting up a separate query that pulls P2 data and relating with a date key for year/work week in its own table
    4. relating P1, P2, and work week tables by the date key
    5. graphing on a Line and Stacked Column Chart with calendar week number on X-axis, and P1 and P2 data on Y axis. 

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.