cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
michaeljc70 Regular Visitor
Regular Visitor

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?

43 REPLIES 43
maracles Member
Member

Re: Make a column not sum/count

@michaeljc70 if I understand you correctly you have not created a measure, you are just dragging the columns to the visualisation.

Do avoid the aggregation happening:

1. Find the field in question in the Values field of the Visualisations Pane
2. Click the small arrow to the firght of the column name
3. Click 'Don't Summarise'

Picture below, hope that helps. 

do_not_summarise.png

 

michaeljc70 Regular Visitor
Regular Visitor

Re: Make a column not sum/count

Thanks for the reply. But as I said, there is no option to not summarize it. 

 

In doing some more research, I think the reason it is doing a count is because the column has "N/A" in some rows and it thinks it is a character field.  I transformed it to get rid of the N/As and make the column a number.  Now I see more options like Average, Minimum, etc. but no option not so summarize.

michaeljc70 Regular Visitor
Regular Visitor

Re: Make a column not sum/count

I'll add when I put all 3 fields in a table, it looks right and there is no counts or aggregates.

michaeljc70 Regular Visitor
Regular Visitor

Re: Make a column not sum/count

I changed the period field (so it sorts properly) and used sum on my number columns and it looks right now.  I still don't have an option to not summarize, but since it is showing the right values, I guess it is okay.  

smpd New Member
New Member

Re: Make a column not sum/count

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

ssaurabhdhingra Occasional Visitor
Occasional Visitor

Re: Make a column not sum/count

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

Lorput Frequent Visitor
Frequent Visitor

Re: Make a column not sum/count

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?

uday64657 New Member
New Member

Re: Make a column not sum/count

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.

Microsoft v-esdev
Microsoft

Re: Make a column not sum/count

Hi All,

 

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

 

Please help me on this.

 

Thanks,

Eswar

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors