POWER BI LINE CHART LEGENDS WITH TWO MEASURES IN Y AXIS

[Maximous89]

Hello guys,

i have a complex situation i wish to help me with,

i have a line chart that have y axis of a measure = (profit/ PY profit),  this line chart is been legend by item, so i could visualize all items in the same chart by line colors, now i'm trying to add line for a new measure b =  calculate((profit/ PY profit), removefilters(item)), but i can't add any measure while the item is added to legend, any ideas?!!

[pcoley · Accepted solution]

To add a line representing the overall profit ratio (ignoring the item filter) to your line chart while keeping the legend for individual items, you can't directly add your measure B alongside the original measure when the item field is in the legend—Power BI will attempt to split measure B by item as well, leading to duplicated or incorrect lines.
Instead, use a disconnected table to create a custom legend that includes an "All Items" option, combined with a conditional measure that switches behavior based on the legend value.

Step 1: Create a Disconnected Legend Table

This table will include all your existing items plus an "All Items" row. It won't be related to your main data model.

• In Power BI, go to Modeling > New Table and enter this DAX:

  ItemLegend = 
  UNION(
      VALUES('YourItemTable'[Item]),  // Replace 'YourItemTable' with your actual table name containing the Item column
      ROW("Item", "All Items")
  )

  This creates a single-column table with all unique items and an extra "All Items" row.

Step 2: Create a Conditional Measure

This measure will calculate the item-specific ratio for individual items but switch to the overall ratio (ignoring item filters) for the "All Items" legend value.

• Go to Modeling > New Measure and enter this DAX (assuming your original measure is named [Profit Ratio]; adjust names as needed):

  Conditional Profit Ratio = 
  VAR SelItem = SELECTEDVALUE(ItemLegend[Item])
  RETURN
      IF(
          SelItem = "All Items",
          CALCULATE([Profit Ratio], REMOVEFILTERS('YourItemTable')),  // Ignores item filters for the total line
          CALCULATE([Profit Ratio], 'YourItemTable'[Item] = SelItem)  // Filters to the specific item
      )

  Replace [Profit Ratio] with your actual measure name (e.g., the one calculating profit / PY profit).
  • Replace 'YourItemTable' with your actual table name.

Step 3: Update Your Line Chart Visual

• Remove the original Item field from the Legend well.
• Drag ItemLegend[Item] into the Legend well instead.
• Remove your original measure from the Values well.
• Drag the new [Conditional Profit Ratio] measure into the Values well.
• Ensure your date/time field is still in the Axis well.

This setup will display colored lines for each individual item (using the item-specific calculation) plus a separate line for "All Items" (using the overall calculation). You can customize the "All Items" line's color, thickness, or style in the visual's formatting pane to make it stand out (e.g., make it thicker or dashed).

[Maximous89]

i salute you! 🫡 @pcoley