How to add a field value from a column in secondary axis

[muktapurc]

How to add a filed in line and stacked column chart

2 hours ago

I have a line and stacked column chart plotted against year and annual product .

X axis -- year wise data plotted 

Y axis -- annual production 

Legend --- categories 

Now I want to add a field from a column which contains different values like 2022-Fs, 2023-fs , 2024- fs 

From these values I need to take only one value and plot it on secondary axis. But I am not getting that option to take a single filed. Instead it is taking entire column into the field values which will result into count 

[Nasif_Azam · Accepted solution]

Hey @muktapurc ,

That is a good observation and make the chart more insightful.

My observation:

When you drag a column like Statusyear directly, Power BI thinks you want all values and it auto-aggregates (count, distinct count, etc.). So you can filter inside the DAX expression, limiting the data to only the value you needed.

To add a single value from a column to the secondary axis in a Line and Stacked Column Chart in Power BI, I am confident on this approach:

You need to create a measure that extracts only the value you want and use that in the chart. 

1. Create a Measure:

SelectedFSValue =
CALCULATE(
    SUM('YourTable'[YourNumericField]), -- Replace with the field you want to plot
    'YourTable'[YourTextColumn] = "2023-FS" -- Replace with your target value
)

1.1. If you’re not summing anything but rather plotting a value directly:

SelectedFSValue =
CALCULATE(
    MAX('YourTable'[Annual Production]),
    'YourTable'[StatusYear] = "2023-FS"
)

1.2. You can also filter it further by year:

SelectedFSValue =
CALCULATE(

    MAX('YourTable'[Annual Production]),
    'YourTable'[StatusYear] = "2023-FS",
    'YourTable'[Year] = MAX('YourTable'[Year])
)

2. Add Measure to the Chart
Go to your Line and Stacked Column chart, Drag SelectedFSValue to the Line values field, and Then enable the Secondary axis for that value from the format pane

3. Format and Adjust
Go to the Format pane > Y-axis, Enable Secondary axis for the SelectedFSValue measure, and Customize the line color, stroke, etc., for clarity

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[muktapurc]

First of all thank you so much Nasif . Your explanation is very clear and easy to understand. 

In case if we want to make CALCULATE(

    MAX('YourTable'[Annual Production]),

    'YourTable'[StatusYear] = "2023-FS",

    'YourTable'[Year] = MAX('YourTable'[Year])

)

Here instead of hardcoded value 2023-fs . How can we make it dynamic. 

[Nasif_Azam]

To make this dynamic you need a disconnected table or parameter table then use the slicer for the table. 

1. Disconnected Table

StatusYearTable = DISTINCT(SELECTCOLUMNS('YourTable', "StatusYear", 'YourTable'[StatusYear]))

Or manually define it like:

StatusYearTable = DATATABLE("StatusYear", STRING, {{"2022-FS"}, {"2023-FS"}, {"2024-FS"}})

2. Add a slicer using StatusYearTable[StatusYear].
3. Update your measure:

SelectedFSValue :=
VAR SelectedStatusYear = SELECTEDVALUE(StatusYearTable[StatusYear])
RETURN
CALCULATE(
MAX('YourTable'[Annual Production]),
'YourTable'[StatusYear] = SelectedStatusYear,
'YourTable'[Year] = MAX('YourTable'[Year])
)

For more information about this concept visit DAX Pattern | SQLBI

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[freginier]

You cannot directly drag a text field and tell it to plot "only one value" on an axis. Instead, you create a DAX measure that calculates a numerical value only for the specific condition (e.g., the year 2023) you're interested in, and BLANK() for all other conditions. This measure then plots as a line on the secondary Y-axis, appearing only at the relevant year.

[muktapurc]

Could you provide me the solution in the form of dax measure. It will be helpful in understanding more clearly. 

[freginier]

FS_2023_Target_Dynamic =
VAR SelectedYear = SELECTEDVALUE('Calendar'[Year])
VAR TargetProduction =
CALCULATE(
SUM(YourTable[Annual Production]), // Your main production measure
FILTER(
ALL(YourTable), // Remove existing filters for the category, if needed
YourTable[IndicatorColumn] = "2023-fs" && YEAR(YourTable[DateColumn]) = 2023 // Filter for the specific year and indicator
)
)
RETURN
IF(
SelectedYear = 2023,
TargetProduction,
BLANK()
)