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
uday64657
New Member

Any solution for this Problem.

I am also facing same issue. In Power BI desktop, I can able to see column with Sum that allowing to show actual count in Graph. However when I publish same to Power BI Online, it broken and showing count of the column.

natabird3
Continued Contributor
Continued Contributor

Make sure to change the column to decimal format then you will be able to change to don't sum. 

Hi All,

 

I am also facing same issue. does anyone find solution for this.

 

Please help me on this.

 

Thanks,

Eswar

Click on the element you don't want to summarize, then go to the modeling tab, around the middle are the properties, select default summarization, don't summarize. Worked on Version: 2.65.5313.701

 

Check the data format within your Excel document.  I've found that if the values in my tables in Excel are not "Number" (e.g. if they are listed as "Accounting" or "General" or "Custom"), then Power BI doesn't recognize the data as values, and your only options for pie or bar charts is either "Count of (data field)" or "Sum of (data field)", etc.

Hope this helps!

 

 

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

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

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.

2020-05-28 09_43_28-Clipboard.png

I can't see that option. Please help

natabird3
Continued Contributor
Continued Contributor

@smemusi 

You should try selecting the value that you need in the fields column, after which in the top menu select the summarization that you desire (dont summ for isntance). and it should solve your problem, hoep this helps.

1.JPG2.JPG

Perfect!  That was the answer.   Thanks so much for posting. Your answer keeps on giving!

smpd
New Member

You need to go back to the table/query click on the dots to the right and select edit query

 

When the data list is displayed, find the column that you want to sum. right click on the column title to select the column and display the menu, then select "change type" and select the appropriate type such as "decimal number"

 

Click the "Close and Apply" button to update the query.

 

Now when you click on the field in the "Values" section you should have all the options

This helped on some columns that were marked number and when I switched t it to text, it removed the sum.  Thanks.  However not working on all of my columns.

This is the answer that fixed the problem for me. There should be a way to mark this as the correct or most accepted answer the way that StackOverflow has it. Thanks!

Hello,

 

I am new to Power BI, so need expert help with the same issue. Earlier I was seeing only Count option, but after following your suggestion, I could see all other options except "Don't Summarize" like Sum, Average, Median etc.

 

I tried different data types like Decimal, Whole Number, Text and Duration but with no luck.

 

Could you please help me in finding the "Don't Summarize" option.

 

Thanks in advance

I am also new to Power BI and am having the same issue with a scatterchart.  I have cost on one axis and usage on the other, and it insists on somehow summing my data.  How can I get to show me the proper range of data?

ngupta
Frequent Visitor

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

 date works in bar chart but not in scatterdate works in bar chart but not in scatter

 

I have this issue too. I tried all the suggestions but still get this count. Here you can see that it is labled as do not summarize and as text. This Location field is city, state combo. As you can see it is doing this for both the location field and the in/out texas field.

 

 

MS BI not correct.PNG

 

@nzipp It'd be better to start a new thread than continue on the end of an old one but...

 

A scatter chart requires each axis to be numeric. You can't put text fields on the axis (unless you want to count them, which is what it's doing). You might want to look at a different type of visual, such as a matrix, depending on what your end-goal is.

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

It's not an old thread as it is still actively being posted to; 4 hours ago and last month for example, by you Leo. Also, when creating dashboards everyone gets tired of looking at the same style graphs and it's important to vary it up with other styles. I have worked with numerous reporting tools and they have all been able to use scatter in this fashion; as seen below.

 

scatter plot.PNG

Anonymous
Not applicable

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

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.