How do I stop "count of" for a line chart with two values Year and Spend?

[Anonymous]

I am working with a data set that I have input manually and I cannot seem to get the Year and Spend columns to show on a standard bar chart. Power BI keeps formatting it as a "Count of" spend when I put anything into the values box of the visual. All I am looking for is a way to show the amount spent per MAT code in this timeline.

This is a screenshot of the data set https://i.imgur.com/iUFh39O.png I have tried changing the data type and other diagrams and so on.

My two questions are: is there a fix? and if not why and when does Power BI change the data to "count of" data?

[v-jayw-msft]

Hi @Anonymous ,

Right click on the value field and you could choose to show the value as sum, average, count or other options.

However the value will always be aggregated if the value one x-axis is the same.

So if your table is like:

2022 1

2022 2

2022 3

The result would be like:

2022 6

It is by design.

Best Regards,

Jay

[Anonymous]

Why is that by default? I mean right now I have a data set that shold clearly be represented as a line but it will only show the data as a "count of" type of table. For example here is only of my rows and I cannot get this data to plot as a line across time. It keeps gettign categorized as a "count of"

[MLorenzen]

Hello!

If I'm understanding you correctly, you want something like the following?

Year	Spend
2022	SUM(Spend)

This sounds like Power BI is defaulting the "Spend" field's aggregation to counting, which you can change to summation on either the visual itself or on the report as a whole (which will require re-adding that field into your visual, as each visual tracks the aggregation of its values independently).

To change it on the visual itself, right-click on the "Spend" field in the "Values" section to see what your aggregation options are, and select "Sum" like this:

If you want to change the default aggregation method for the "Spend" field across the whole report, you can do so by clicking the field in your "Fields" pane and adjusting that behavior to "Sum" in the ribbon that gets navigated to automatically:

I hope this helps!!

[davehus]

Hi @Anonymous ,

If you right click on the field that is showing count in your visual, you will be presented with some options as below.