cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AWeichselbaum Occasional Visitor
Occasional Visitor

Re: Make a column not sum/count

I just figured this out.

 

Under Fields, click on the field which is being auto-summarized. Once selected, go to the top ribbon and click "modeling". You will then see a button called "Default Summarization..." click this, and select 'do not summarize'. Above that button you can also change what the data is - this is useful for telling Power BI that the data is a postal zip code, for instance.

Rolandek Frequent Visitor
Frequent Visitor

Re: Make a column not sum/count

Change the type of the field in the query to Decimal or whole number from ANY. it worked for me

Rolandek Frequent Visitor
Frequent Visitor

Re: Make a column not sum/count

Change the type of the field in the query to Decimal or whole number from ANY. it worked for me

ChrisGrace Visitor
Visitor

Re: Make a column not sum/count

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.

 

THkY82RDon't summarize in tablesa2DzlEKNo choice in Line Chart

ngupta Frequent Visitor
Frequent Visitor

Re: Make a column not sum/count

I am also struggling with aggregation. I do not want to summarize my data, and I want to see all data points. Even after I have clicked on "Do not Summarize" in modeling tab, my data is still getting summarized. only options are count or distinct count.

 

Even year is getting counted??

 

This is weird, if I use one graph, dates work fine, but with other it doesn't. Never had this issue in tableau..

 

Please help

 scatter vs barchart.JPGdate works in bar chart but not in scatter

 

alexadams78 Frequent Visitor
Frequent Visitor

Re: Make a column not sum/count

I think I had a similar problem - I have a list of Facebook Fans, broken down by day. As a number field, it would sum/count/average etc...but I then changed this column to a TEXT field in Power BI, and this worked - giving the options of First or Last in the values field, which was exactly what I needed.

 

 

Anonymous
Not applicable

Re: Make a column not sum/count

Hi, I am also struggling with this same issue. have you got solution? 

leonardmurphy Established Member
Established Member

Re: Make a column not sum/count

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 Microsoft BI Professionals - Wisconsin group.
superuser Frequent Visitor
Frequent Visitor

Re: Make a column not sum/count

I found it here:

 

Under modelling tab, default summarization: Do not summarize

superuser Frequent Visitor
Frequent Visitor

Re: Make a column not sum/count

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

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 277 members 2,314 guests
Please welcome our newest community members: