cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AndrewP
Helper I
Helper I

Option to choose "Don't Summarize" Missing. "Do not summarize" is not honored.

I'm trying to create a line chart or scatter plot

 

I have a column which is numerical and contains the (varying values) data I wish to plot

I have that column set to

  Data Type: Whole Number

  Format: Whole Number

  Default Summarization: Do Not Summarize

 

My other column is an ID column containing mostly sequential integers

I have that set the same way as above.

 

I want to plot along the horizontal axis, the ID numbers, and then have a line showing the varying values.

 

I cannot get the values I have to show up.  It insists on choosing Sum, or Count, and does NOT give me the option for "Don't Summarize".

 

Please help me get this working.

2 ACCEPTED SOLUTIONS

If you have just one metric per ticket number, then SUM doesn't matter.  You are SUMMING 1 data point.  Your example data showed a column of distinct ticket numbers and their hours open, so if you put that distinct list of ticket numbers on your axis, SUMMING the hours open will not show incorrect data.  If you have multiple records for ticket numbers, then you need to choose an aggregation or duplicate numbers on the axis.

 

I think where you are getting confused is that the SUM operation applies to the metric for each point on the X axis, not across the whole data set.

View solution in original post

Hi @AndrewP,

 

The line chart visual is used for displaying summarize data. You can image the line chart Axis property is the column group of the matrix, Legend property is the Row group of the matrix. If there were more than one record for the same intersection of the column and row groups, we need to aggregate the values. So there are many aggregate functions for summarize the data instead of "Do not Summarize".

 

The "Default Summarization: Do Not Summarize" option apply to the field itself. Once we place the filed into visuals, it will depend on the visual design to use this. In chart visual, "Do Not Summarize" is not available but in a table visual, it can support.

 

In your scenario, as your sample data contain one record for each Axis values, use the SUM() function will also display the raw data. But if there are duplicate record for each Axis, SUM will aggregate the values.

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

21 REPLIES 21
Anonymous
Not applicable

Even i got the same problem. I set Don't summarize in table but visuals->values pane it is automatically taking count. I dont want to count my tickets. 

Just in case someone else comes across this, the original question was never answered correctly.

 

A lot of users think that setting the table column in the Fields section to "Don't Summarize" should address the issue, but there are 2 layers of possible summarization:

 

  • At the table level
  • At the visual level

The table/column setting is seen here:

SeanTavares_0-1634447426789.png

 

...buy you also need to check for summarization on each visual here:

SeanTavares_1-1634447705809.pngSeanTavares_3-1634447839461.png

 

I've seen this trip up a lot of novice report designers.

 

 

Thank you Sean for your inputs. In my case, since its individual visual level, the 2nd image helps. However I could find all options like Sum, Average etc except 'Don't summarize'. I am not sure what's the issue with my data that it doesn't show that. Also I miss the conditional formatting option in context menu although I feel it's not shown because the data type of the field is number

 

The Visual I use is Line chart visual if that is the reason I could not see 'dont summarize' I don't know.

parry2k
Super User
Super User

I don't think you can do Don't Summarize on graphs. 

 

As per my understanding, "Don't Summarize" in modelling helps when you drop the filed on canvas what should be the default behaviour, like count or sum, but if you are using it on graph, it is going to use one of aggregation method.

 

Could you elaborate why you don't want to summarize? There might be other way to do so like you can use max or min.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





I'm failing to see how a graph is useful then.

 

For example, I want to get a line chart showing how long it takes tickets to be closes based on their start and close time.

 

How is an average useful for a line graph of this data in the least?

Can you share the example data?

 

I'm still not sure what you want to show on axis? How you want to measure? In terms of number of days/hours/minutes? What is the unit? 

 

Can you share what output you like to see?






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Ticket ID, Hours Open

 

1,5

2,10

3,2

4,12

5,12

6,2

 

I want something like this showing the time open over the course of the date range I'm using.

 

EDIT: picture isn't uploading here, so http://i.imgur.com/CQHX66j.png

You want to achieve within a date range how many tickets were open by "Open hours", in your example,

 

2 tickets opens for 12 hours

2 tickets open for  2 hours

1 ticket open for 5 and 10 hours,

 

Is this what you expect as a result on graph?






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





ticket.PNG






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





That says Sum of hours...I don't want to sum anything at all.  I want to use the raw numbers.

Ok, let me ask you this, apart from saying "Sum of hours", can you confirm if graph is showing what you are looking for?






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





The graph looks like it's showing what I'd expect, but wouldn't that mean 'sum' isn't working correctly then, if not what exaclty IS the difference between sum and no summarizing?

As i mentioned the summarization method you set for a field is to set up default behaviour. In graphs, you are measuring your data and measure should have some aggregation, like sum/avg/min/max etc...

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





I'm not sure I follow.

From my example, isn't that a perfect example of when I would NOT want this behavior?

 

I have other sections where the behavior makes perfect sense, such as amount of tickets opened per top 30 users.

I believe amount of tickets you mean total count of tickets and total hour spent by user, correct?

 

So in this case, you are counting ticket and doing the sum on hours, you need to use  COUNT and SUM to summarize data by user.

 

I'm still fully not sure what exactly you are after? May be someone else can better understand and explain.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





I did mean count in that case.

I was saying I understand the need to aggregate in graphs.

 

But the case I'm asking about, with the hours, should not be aggregated, no?  Why would I want to sum the values, when I'm looking for a line graph that shows fluctuations.

 

(Those were two separate examples)

Hi @AndrewP,

 

The line chart visual is used for displaying summarize data. You can image the line chart Axis property is the column group of the matrix, Legend property is the Row group of the matrix. If there were more than one record for the same intersection of the column and row groups, we need to aggregate the values. So there are many aggregate functions for summarize the data instead of "Do not Summarize".

 

The "Default Summarization: Do Not Summarize" option apply to the field itself. Once we place the filed into visuals, it will depend on the visual design to use this. In chart visual, "Do Not Summarize" is not available but in a table visual, it can support.

 

In your scenario, as your sample data contain one record for each Axis values, use the SUM() function will also display the raw data. But if there are duplicate record for each Axis, SUM will aggregate the values.

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I have in a table month as 1 to 12 and year 2016, 2017 and based on this i have made a YTD calculation

. in the table the caclulation is showing correct data, as running total also but if I try to build a chart, showing YTD values across months, it display the wrong values, as I do not have the option to " Do not Summarize" as I have in the table display. 

If you have just one metric per ticket number, then SUM doesn't matter.  You are SUMMING 1 data point.  Your example data showed a column of distinct ticket numbers and their hours open, so if you put that distinct list of ticket numbers on your axis, SUMMING the hours open will not show incorrect data.  If you have multiple records for ticket numbers, then you need to choose an aggregation or duplicate numbers on the axis.

 

I think where you are getting confused is that the SUM operation applies to the metric for each point on the X axis, not across the whole data set.

Hi Thanks for the explanation. Have a doubt:

//then you need to choose an aggregation or duplicate numbers on the axis.//

I don't want aggregation so how to choose duplicate numbers on axis ?

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

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

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.