BASICS HELP How to equal a column so that it can be used as a value in a chart organized by date

[lovekheart]

Hello my raw data is column A = {Month} and column B = Totals. How can i create a measure that equals the values in the column B given {MONTH} has all distinct values and {Totals} are varied. I need to be able to create a line chart with (year month)=[already a seperate measure] as the x axis and this new created measure (Column B) as the y axis. 

Ive tried this below but it didnt work 😞
 ValuesofTotals =
VAR TotalsValues = VALUES('RAWDATA'[Totals])
RETURN CONCATENATEX(TotalsValues, [Totals], ", ")

[v-tejrama]

Hi @lovekheart ,

Thanks for reaching out to the Microsoft fabric community forum.

Hey, the issue here is that your original formula was returning text using CONCATENATEX, but for a line chart, the Y-axis needs a numeric value like a sum or total.

The correct approach is to create a measure using SUM('RAWDATA'[Totals]), which gives you a proper number for each month. Also, to show the months clearly on the X-axis, it's better to create a custom column like YearMonth = FORMAT([Month], "YYYY-MM"), so Power BI can group and display the data correctly.

Once you’ve done this, just bring the YearMonth column into the X-axis and the Total Amount measure into the Y-axis of a line chart. This will correctly show one data point per month, exactly as you wanted.

Avoid using the default date hierarchy, and use your own YearMonth column for full control. 

Please find the attached pbix file for your reference.

Best Regards,
Tejaswi.
Community Support

[v-tejrama]

Hi @lovekheart ,

Just checking in have you been able to resolve this issue? If so, it would be greatly appreciated if you could mark the most helpful reply accordingly. This helps other community members quickly find relevant solutions.

Thank you.

[v-tejrama]

Hi @lovekheart ,

Just wanted to check if you had the opportunity to review the suggestion provided?

Thank you.

[v-tejrama]

Hi @lovekheart ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[DataNinja777 · Accepted solution]

Hi @lovekheart ,

Your CONCATENATEX formula creates a text string, but a chart's Y-axis requires a single numerical value for each data point. To fix this, you need to use an aggregation function that tells Power BI how to summarize the [Totals] for each month.

The most straightforward and recommended approach is to use the SUM function. This function will add up all values in the [Totals] column that correspond to each month on your X-axis. Even if there's only one value per month, SUM is the standard and most reliable method.

Total Amount = SUM('RAWDATA'[Totals])

To use this, create a new measure in your table and paste in the formula above. Then, build your line chart by placing your [year month] field on the X-axis and dragging your new [Total Amount] measure to the Y-axis. This will correctly plot the total for each month.

If you are absolutely certain that there is only one row per month in your data, you could use SELECTEDVALUE as an alternative. This function returns the value only if it's the single, unambiguous value in the context; otherwise, it returns blank.

Total Amount = SELECTEDVALUE('RAWDATA'[Totals])

Best regards,