cancel
Showing results for 
Search instead for 
Did you mean: 
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?

46 REPLIES 46
maracles
Resolver II
Resolver II

@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

 

Hello,

Thank you for the reponse - it makes a ton of sense. The issue I'm experiencing is there is "Don't Summarize" option.

 

The options listed are:

 

- Sum

- Average

- Minimum

- Maximum

- Count (Distinct)

- Count

- Standard Deviation

- Variance

- Median

 

Thoughts? 

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.

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

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.  

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors