Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
michaeljc70
Advocate II
Advocate II

Make a column not sum/count

I've used Power BI quite a bit lately with a SSAS cube and some Sql Server queries and created many visualiations.  I am still fairly new to this though and cannot figure this out. 

 

I imported a very simple Excel spreadsheet. 3 columns: period, actual, goal. I tried to make a line and stacked column chart.  However, it keeps using the aggregations (that don't make sense). It is using a count of the goal and count of the actual and not the value from the spreadsheet.  

 

When I go into the query, I see no place to change how/if a column is summarized. I know how to do this in PowerPivot.  

 

Beyond that, I have no idea why it would do any aggregations since I am using all fields in the dataset and aggregation is not needed/appropriate.

 

When I click on the column under "Column values"  it shows "Count of Actual" and the only options are "Remove Field",  "Count (Distinct), and "Count".  How do I kjust get the value?

47 REPLIES 47
malrawi
New Member

I can't format it as date as I only have the year (eg, 2022). 
The only way around this is to format the years as text, then, change from sum to count. 

cfh
Frequent Visitor

When I needed a chart to pick up the most recent value on a metric with daily values but didn't have a "don't summarize" option in the visual, I left it as Sum but added a Top N date filter to the visual, chose top 1 day and by value Latest date. Summing the most recent date's data with no other values did what I needed.

 

(Setting the column's default summarizaton to don't summarize didn't help, and I couldn't store the values as text since I needed them to drive a chart axis.)

 

I realize this is a very old question, and I'm not sure I understand OP's goal, but I wonder if @michaeljc70's challenge would be solved by doing the visual filter trick using his period column.

born2see
New Member

Another unfortunate soul who can't get rid of the Sum of my data.  Why does this program insist on summing these numbers???  My chart is so simple. A date field and a value field.  I just want to plot them over time but this program inisists on summing my data.  I've tried everything posted here and it just doesn't work.

 

Any thoughts before I go find some other program?

 

Thanks

So, I realized my error was caused by an incorrect data read in Power Query. I had input just the YEAR into a column which was being interpretted as a Whole Number rather than a Date. When I formatted my column to a DATE, I was able to filter by YEAR and that fixed the Sum Total problem.

 

I'd recommend taking a look at your data sets in Power Query, and making sure all your columns are formatted the way you need them to be - this will ensure it is reviewed properly.

 

born2see
New Member

Another unfortunate soul who can't get rid of the Sum of my data.  Why does this program insist on summing these numbers???  My chart is so simple. A date field and a value field.  I just want to plot them over time but this program inisists on summing my data.  I've tried everything posted here and it just doesn't work.

 

Any thoughts before I go find some other program?

 

Thanks

Deruth58
Regular Visitor

I was having the same issue, and found a work-around that works for me.  Not sure if it will work for everyone having this issue, but it may.   See my post here:

 

https://community.powerbi.com/t5/Desktop/Bug-Line-Chart-Behavior-When-Plotting-Data-Instead-Seems-to...

 

Doug

panoslaz
Helper I
Helper I

Hi all, 

I have a similar problem. 

I imported 2 datasets, fixed the relationships and made sure the fields I need are formated as decimals. 

In 1st dataset I have project name and Metric1 and in the 2nd project name and Metric2. 

I want to create a scatter plot with the 2 metrics. When I try this, X Axis (metric 1) works pretty good and adjusts the position of the point according to the metric value. For metric2 though, no matter what I choose from the value drop down menu, gives me the total average/sum/count of all metric2. I choose Dont summarize but still no fix. 

 

Can you help?

Thanks 

Can you upload a screen shot of your headers and maybe row 1 of your dataset?

 

 

RodgerB
Frequent Visitor

I've been beating my head up against a wall.  I have a revenue total in one table, and an average in another table.  Both are joined via a table with common fields.  They work, as I can drag the fields from the common field, and everything works as it should.

 

No matter what I do, when I drag the average field from one table to a combo chart line value, it ALWAYS sums.  I've tried the above help and told it not to summarize, but now it counts.

 

Is there any way to do this?

 

Thanks!

 

Rodger

PaulChandler
New Member

Hi

am trying to compare a tender sum and a final cost on a series of projects - any suggestions how best to do this?

Is your data in different tables? If so, make sure your cross filter direction is set up both ways in the relationship view. I have one table with sales data, one with purchasing data and one project data. All have project ID number in them and relations go from one to another with cross filtering set in "both". If the cross filtering is "single", my sales amount values were shown as counted, not as per sales ID.

rayaneid
Regular Visitor

Hi Michael,

 

You will need to make sure that the type of the field that you are fetching the data is a whole number and not a Text.

 

Go to your query editor and when the results are displayed, just right click on the column that you need then go to Change Type - > Whole Number. Remove the field then add it again to your chart and this shall solve your problem

rayaneid
Regular Visitor

Hi Michael,

 

You will need to make sure that the type of the field that you are fetching the data is a whole number and not a Text.

 

Go to your query editor and when the results are displayed, just right click on the column that you need then go to Change Type - > Whole Number. Remove the field then add it again to your chart and this shall solve your problem

russellwsmith
New Member

I would also appreciate a solution to this.  I'm just trying to plot a time series and it bins by day rather than showing multiple months at a time.

@russellwsmith It'd be better to start a new thread with more information about what you are trying to accomplish & what you've tried so far. Your 2 sentences aren't enough to be able to do anything more than guess at what might be wrong.

 

If it's not a problem, but rather you think Power BI should work differently, submit an idea (or find an existing one to support).

 

---

 

Overall, I recommend starting a new thread rather than posting here because a new thread can be exclusively focused on what you're trying to accomplish, and once you've solved your problem, you can mark the thread as answered. By contrast, this thread is muddied by lots of people trying to do lots of different things, and the original poster has long since moved on. This isn't a problem for which one answer neatly applies to everyone; the answer really does depend on what you're trying to do, and why you're trying to do it.

 

---
In Wisconsin? Join the Madison Power BI User Group.
superuser
New Member

Its under modelling tab, default summariation: Do Not Summarize.

superuser
New Member

I found it here:

 

Under modelling tab, default summarization: Do not summarize

leonardmurphy
Skilled Sharer
Skilled Sharer

Most visuals in Power BI expect an aggregated value in the "Values" section of the chart. The exception (I know of) is the table visual.

 

For that reason, if you put a column into the "Values" section of a chart, Power BI will select an aggregation for you. You can change that aggregation (sum, average, count, min, max, etc) but you cannot select "Do Not Summarize". 

 

If you think about it, this does make sense. Take, for example, a column chart. If you have "Month" on the axis and then you put "Sales" as the value, what do you expect Power BI to do when there is more than one sale in a given month? It needs to know if it should sum, average, count, etc. Even if you know there's only one data point per month, Power BI doesn't know that will always be true.

 

There are a few ways around this. If you want to break your column chart down to have a single column for every sales record, then you need sales record ID (or another attribute that's unique for every record) on your axis. If, as in the case of the original poster, you want to break down your actual & goal by period, then period needs to be on the axis.

 

If you know that you have only 1 value per period (say), then using an aggregation such as average or sum shouldn't matter because an average/sum of a single value is that value (as long as you're breaking down your chart by period).

 

For a scatter chart, the same principals apply but in this case, there is no axis. Instead, you want to put the field you want to break your chart down by into the "Details" or "Legend" section.

 

In any of these cases, if you don't have a field to break your chart down to the level you want, you'll need to add one. This could be as simple as going to "Edit Query" and adding an Index column (though I'd recommend adding something that makes sense to your problem domain).

 

Should Power BI have a concept of "row" when displaying data, so you can de-aggregate data to the lowest level without having to manually add your own column? I personally don't think so. Power BI is designed as an interactive data analysis tool. For that reason, charts need to provide context (such as what a column represents). Even if each row represents a single customer or a single month, it makes more sense to provide the customer ID or month name in your chart, than for Power BI to use a meaningless row number. In my opinion, it's good that Power BI puts the onus on you to add that context as a column to your data.

 

 

---

 

 

Bear in mind, with some of my de-aggregating suggestions, that if you're working with large datasets, Power BI isn't going to perform all that great. Power BI excels at aggregation. A column chart with 10,000 columns is going to be pretty unusable. A scatter chart with 100,000 data points might not perform as well as a traditional graphing tool. It may be worth exploring other tools if that's your end goal (R, Excel, the Sand Dance custom visual).

 

As other posters have mentioned, data types are important. If your column is numeric, Power BI is generally going to assume that it's a "value" and try to aggregate it. For something like "Period", where you don't want to aggregate it, you will have to deliberately move the column to the axis part of the visual. This also applies in reverse. If your "Goal" is a text field, then Power BI has limited options for aggregation (it can do a first/last/count, but it cannot sum or average a text field).

 

Overall, I recommend using measures that explicitly sum/average/count things, rather than dragging columns onto visuals and choosing the aggregation on each visual. Reasons: (1) Having a measure for Customer Count saves you from constantly having to take CustomerID and change a sum to a count. (2) It reduces the risk that someone sums a column and thinks it is a count (sometimes it is obvious, but not always). (3) If you later want to redefine who counts as a customer, you can update the measure and all visuals will adjust automatically.

 

For attributes you would only ever want to use on your axis/legend, I recommend setting the default aggregation in the modeling tab of the data window to "Do Not Summarize" as AWeichselbaum mentioned above. This is indeed useful if you have a ZIP column and you know you never want to sum 2 zip codes together, and want to be able to select Zip Code without it aggregating.

 

---

 

Hope this helps as a summary of what might be going on when you find yourself unable to de-aggregate a column. If you're still stuck, I recommend posting a new thread with exactly what you're trying to accomplish (and what isn't working) rather than continuing to ask in this thread.

---
In Wisconsin? Join the Madison Power BI User Group.
ChrisGrace
Regular Visitor

I have been fighting this as well.  It makes a difference which visualization you are adding the values to.  I show the "Don't Summarize" option on the Table, but not the Line Chart.

 

Don't summarize in tablesDon't summarize in tablesNo choice in Line ChartNo choice in Line Chart

I've manged to get the SUM menu option to appear by simply formatting the columns in your orginal data file (i.e. excel or SQL) to be decimal format type. Once this is done, re import the data table to power BI and the SUM menu option should appear. Hope that helps.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.