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

Highlighted

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.

 

Highlighted

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

Highlighted
Continued Contributor
Continued Contributor

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

Highlighted

2020-05-28 09_43_28-Clipboard.png

I can't see that option. Please help

Highlighted
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

Highlighted
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.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors