Forum Discussion

JoshS's avatar
JoshS
Frequent Visitor
2 years ago
Solved

Comparing line graphs with different x-axes (dates) on the same graph

I am looking to create a single graph that compares 4 different values. The problem I am running into is each value is based off a different date column, which I am looking to combine. Currently, the data exists in 4 seperate graphs as shown below:

 

 

I've created dummy data in excel to demonstrate how the data currently exists in Power BI, and the transformation I am hoping to achieve:

Any help in this would be greatly appreciated.  


Need Help
  • Irwan's avatar
    Irwan
    2 years ago

    hello JoshS 

     

    please check if this accomodate your need.

     

    1. create a new date table.

    Table 2 = CALENDAR(DATE(2024,6,20),DATE(2024,6,28))
    I put minimum date as 20-Jun-2024 and maximum date as 28-Jun-2024.
     
    2. create calculated column for average stage 1
    Average Work Days in Stage 1 = AVERAGEX(FILTER('Table','Table'[Stage 1 Completion]='Table 2'[Date]),'Table'[Days in Stage 1])
     
    3. create calculated column for average stage 2
    Average Work Days in Stage 2 = AVERAGEX(FILTER('Table','Table'[Stage 2 Completion]='Table 2'[Date]),'Table'[Days in Stage 2])
     
    4. create calculated column for average stage 3
    Average Work Days in Stage 3 = AVERAGEX(FILTER('Table','Table'[Stage 3 Completion]='Table 2'[Date]),'Table'[Days in Stage 3])
     
    5. create calculated column for average production
    Average Work Days in Production = AVERAGEX(FILTER('Table','Table'[Stage 3 Completion]='Table 2'[Date]),'Table'[Work Days in Production])
     
    so here are what i found weird in your sample data: 
    1. in your desired table at stage 2, the average on 25-Jun-2024 is 2,5. However, if I looked at your power bi data, on 25-Jun-2024, the data are 3 and 1. The average should be 2.

    2. From what i see, the Average Work Days in Production is taking same date as Stage 3 Completion.

     

    Hope this will help you.

    Thank you.

2 Replies

  • Irwan's avatar
    Irwan
    Super User
    2 years ago

    hello JoshS 

     

    please check if this accomodate your need.

     

    1. create a new date table.

    Table 2 = CALENDAR(DATE(2024,6,20),DATE(2024,6,28))
    I put minimum date as 20-Jun-2024 and maximum date as 28-Jun-2024.
     
    2. create calculated column for average stage 1
    Average Work Days in Stage 1 = AVERAGEX(FILTER('Table','Table'[Stage 1 Completion]='Table 2'[Date]),'Table'[Days in Stage 1])
     
    3. create calculated column for average stage 2
    Average Work Days in Stage 2 = AVERAGEX(FILTER('Table','Table'[Stage 2 Completion]='Table 2'[Date]),'Table'[Days in Stage 2])
     
    4. create calculated column for average stage 3
    Average Work Days in Stage 3 = AVERAGEX(FILTER('Table','Table'[Stage 3 Completion]='Table 2'[Date]),'Table'[Days in Stage 3])
     
    5. create calculated column for average production
    Average Work Days in Production = AVERAGEX(FILTER('Table','Table'[Stage 3 Completion]='Table 2'[Date]),'Table'[Work Days in Production])
     
    so here are what i found weird in your sample data: 
    1. in your desired table at stage 2, the average on 25-Jun-2024 is 2,5. However, if I looked at your power bi data, on 25-Jun-2024, the data are 3 and 1. The average should be 2.

    2. From what i see, the Average Work Days in Production is taking same date as Stage 3 Completion.

     

    Hope this will help you.

    Thank you.

  • JoshS's avatar
    JoshS
    Frequent Visitor
    2 years ago

    Thanks Irwan, that was exactly what I needed.