Forum Discussion

Rollow's avatar
Rollow
Frequent Visitor
1 year ago
Solved

Line chart with multiple measures with same dimensions

Goodday,

 

I have a table with 2 dimensions and 2 measures that use the same dimension. 

 

 

I would like to make a line chart from this, with showing all lines. But i seem to be unable to add both measures in the line chart. Why is this and how can i fix this? 

Need Help
  • Elena_Kalina's avatar
    Elena_Kalina
    1 year ago

    Hi Rollow 

    Based on your table, you want to create a line chart showing both Measure 1 and Measure 2 across different dimensions over two years. The issue you're experiencing is likely due to how your data is structured and how your visualization tool handles multiple measures.

    Why You Can't Add Both Measures

    Most visualization tools expect data in a "tidy" format where:

    • Each variable forms a column

    • Each observation forms a row

    • Each type of observational unit forms a table

    Your current table has measures spread across columns, which can make it difficult to plot both measures simultaneously.

    How to Fix This

    1. Pivot Your Data (Recommended)

    1. Go to "Transform Data" in Power BI

    2. Select your table

    3. Choose both Measure 1 and Measure 2 columns (hold Ctrl)

    4. Click "Unpivot Columns" under the Transform tab

    5. Rename the new columns:

      • "Attribute" → "Measure_Type"

      • "Value" → "Measure_Value"

    Year       Dimension  Measure_Type  Value
2023-2024  DH         Measure 1     2.6
2023-2024  DH         Measure 2     2.86
2023-2024  GEM        Measure 1     2.5
2023-2024  GEM        Measure 2     2.85
... (and so on for all rows)

    In this format, you can:

    1. Put Year on the X-axis

    2. Put Value on the Y-axis

    3. Use Dimension for color encoding (lines)

    4. Use Measure_Type to create separate lines for each measure

    Step 2: Create the Line Chart

    1. Select the Line Chart visualization

    2. Configure fields:

      • X-axis: Year

      • Y-axis: Measure_Value

      • Legend: Measure_Type (to show both measures)

      • Details: Dimension (to separate lines by DH, GEM, etc.)

    Alternative Solution: Combo Chart

    If you prefer not to transform data:

    1. Select the "Line and Column Chart" visualization

    2. Configure:

      • Shared X-axis: Year

      • Column Y-axis: Measure 1

      • Line Y-axis: Measure 2

      • Legend: Dimension

    If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!  Thank you.

2 Replies

  • Elena_Kalina's avatar
    Elena_Kalina
    Solution Sage
    1 year ago

    Hi Rollow 

    Based on your table, you want to create a line chart showing both Measure 1 and Measure 2 across different dimensions over two years. The issue you're experiencing is likely due to how your data is structured and how your visualization tool handles multiple measures.

    Why You Can't Add Both Measures

    Most visualization tools expect data in a "tidy" format where:

    • Each variable forms a column

    • Each observation forms a row

    • Each type of observational unit forms a table

    Your current table has measures spread across columns, which can make it difficult to plot both measures simultaneously.

    How to Fix This

    1. Pivot Your Data (Recommended)

    1. Go to "Transform Data" in Power BI

    2. Select your table

    3. Choose both Measure 1 and Measure 2 columns (hold Ctrl)

    4. Click "Unpivot Columns" under the Transform tab

    5. Rename the new columns:

      • "Attribute" → "Measure_Type"

      • "Value" → "Measure_Value"

    Year       Dimension  Measure_Type  Value
2023-2024  DH         Measure 1     2.6
2023-2024  DH         Measure 2     2.86
2023-2024  GEM        Measure 1     2.5
2023-2024  GEM        Measure 2     2.85
... (and so on for all rows)

    In this format, you can:

    1. Put Year on the X-axis

    2. Put Value on the Y-axis

    3. Use Dimension for color encoding (lines)

    4. Use Measure_Type to create separate lines for each measure

    Step 2: Create the Line Chart

    1. Select the Line Chart visualization

    2. Configure fields:

      • X-axis: Year

      • Y-axis: Measure_Value

      • Legend: Measure_Type (to show both measures)

      • Details: Dimension (to separate lines by DH, GEM, etc.)

    Alternative Solution: Combo Chart

    If you prefer not to transform data:

    1. Select the "Line and Column Chart" visualization

    2. Configure:

      • Shared X-axis: Year

      • Column Y-axis: Measure 1

      • Line Y-axis: Measure 2

      • Legend: Dimension

    If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!  Thank you.