Forum Discussion

Ignite190's avatar
Ignite190
Frequent Visitor
5 months ago
Solved

Requirement: Line chart with Actual + multiple Forecast Versions (dynamic) from different tables

Hi everyone,
I’m looking for guidance on a Power BI limitation / workaround for a line chart scenario.

Business requirement

I need to visualize:

  • Actual consumption data
  • Multiple forecast versions (e.g., V1, V2, Latest, Approved, etc.)
    on the same line chart for comparison over time.

The expected outcome is:

  • One line for Actual
  • Multiple lines for Forecast, one per Forecast Version
  • Forecast Versions are dynamic (not fixed, new ones can appear)
  • A Legend driven by Forecast Version showing multiple forecast lines at once

This is a standard forecasting comparison use case.

Current data model

  • Actual data comes from Table A
  • Forecast data comes from Table B
  • Forecast table has a column: Forecast Version
  • Actual and Forecast are not in the same table
  • I already have separate, correct DAX measures:
    • Actual Consumption
    • Forecast Consumption

Problem / limitation

  • As soon as I put Actual Consumption and Forecast Consumption together in the Y‑axis, Power BI disables the Legend
  • Because of this:
    • I cannot place Forecast Version in the Legend
    • I cannot show multiple forecast lines together dynamically
  • I understand this is because Power BI doesn’t support Legend + multiple measures in Values

    Is there any supported or recommended way in Power BI to achieve this ?

Need Help
  • danextian's avatar
    danextian
    5 months ago

    Hi Ignite190 

    Line charts in Power BI allow either multiple measures (series) as a legend or categories from a column. That said, is to use a disconnected with a column the actual measure name and the forecast version and reference this column in  a measure. Then use the original version column as a slicer.

    Please see the attached pbix.

     

5 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    5 months ago

    Hi Ignite190 

    Line charts in Power BI allow either multiple measures (series) as a legend or categories from a column. That said, is to use a disconnected with a column the actual measure name and the forecast version and reference this column in  a measure. Then use the original version column as a slicer.

    Please see the attached pbix.

     

  • grazitti_sapna's avatar
    grazitti_sapna
    Icon for Super User rankSuper User
    5 months ago

    Hi Ignite190,

     

    CAn be achived by appending both tables together and then using them in Line chart.

     

    I've created a sample .pbix file for you. Hope this is what you require.

     

    🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
    💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
    🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
    🔗 Curious to explore more? [Discover here].
    Let’s keep building smarter solutions together!

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    5 months ago

    Hi Ignite190 ,

     

    Create a new table with a line saying actuals and the versions fo your forecasts v1, v2 and só on. For this to BE dynamoc you can use Power query or dax.

     

    Then add a new measure similar to this One

     

    Atual + forecasts = 

    IF(SELECTEDVALUE(Newtable[columname]), [Actual], CALCULATE([Forecast]), Forecastable[Forecast] = SELECTEDVALUE (Newtable[Columname])))

     

    Then you can use this measure on the values and the column from the new table has Legend.

     

  • Kedar_Pande's avatar
    Kedar_Pande
    Icon for Super User rankSuper User
    5 months ago

    Ignite190 

    Create Table:

    Combined Data = 
    UNION(
    SELECTCOLUMNS(TableA, "Date", TableA[Date], "Version", "Actual", "Value", TableA[Consumption]),
    ADDCOLUMNS(
    TableB,
    "Version", TableB[Forecast Version],
    "Date", TableB[Date],
    "Value", TableB[ForecastConsumption]
    )
    )

    Combined[Date] → DateTable[Date]

     

    Measure:

    Consumption Value = 
    SUM(Combined Data[Value])

    Line Chart:

    X-axis: Date

    Y-axis: Consumption Value

    Legend: Version (Actual + all dynamic forecasts)

     

  • cengizhanarslan's avatar
    cengizhanarslan
    Icon for Super User rankSuper User
    5 months ago

    Step 1) Create a unified calculated table in DAX

    CombinedTable =
UNION (
    SELECTCOLUMNS (
        TableA,
        "Date",   TableA[Date],
        "Value",  TableA[Value],
        "Series", "Actual"
    ),
    SELECTCOLUMNS (
        TableB,
        "Date",   TableB[Date],
        "Value",  TableB[Value],
        "Series", TableB[Forecast Version]
    )
)

     

    Step 2) Create a single measure

    Consumption =
SUM ( CombinedTable[Value] )

     

    Step 3) Configure the line chart

    • X axis: Date
    • Y axis: Consumption
    • Legend: Series

     

    Step 4) Connect CombinedTable to your Date table

    Modeling tab → Manage Relationships → create a relationship between CombinedTable[Date] and your Date table