Creating Pie Chart

Chad Miller shared this question 2 years ago
Answered

I must be missing something. I have two metrics Cost and Margin and I want to create a Pie chart out of these. One piece Cost the other Margin. What am I missing that I can't get this set up?

Comments (1)

photo
1

Hi Chad,

The process to create a pie chart out of two metrics is as simple as dragging them both into the builder, so I doubt that you are missing anything. What exactly isn't working?


Are you receiving any errors?

Is it not looking the way you intend?


If you have any screenshots that would help me understand where you are at in the process, I might be able to offer more specific advice.


Regards,

Nathan

photo
1

There is no combination when I drag in cost and margin that I ever get two slices. I can only drag 1 item into color and 1 into size which always gives me 1 slice.

photo
1

Thanks Chad, can you show me a screenshot of your data tab?

photo
1

It's basic cost and margin. I want to link this on a dashboard so the numbers will change according to time range selected. I've also added date field to this data but it doesn't help to ever get two slices I've only been allowed to have 1 item in each box on the chart creation.

photo
1

Unfortunately you are going to need to have each of your metrics in one column. Right now you are seeing that cost is 100% of itself, and is the color of margin. To get this to work you will want the data in this format:


Type | Amount

Cost | 50,000

Margin | 25,000


I think that there is a little trick to do this conversion in one report, but its not on the top of my head. If you can give me a few minutes I will try and mess around with this. Hopefully I will have something useful for you soon here.


Regards,

Nathan

photo
1

So to do this we will need to:


1. Create a view level calculated field called "cost" that is simply "cost", and another called margin:

96e5b72c25f6e845cdbe043a131ccf67


2. Create a union sub-query, joining on these calculated fields (cost=margin) and your pre-existing fields(cost=margin):

2745d2cb5259ec9775563af7ed6906d8

Which will give you the amounts in the proper format. Now the metrics can be size, and the labels(cost/margin) can be your color.


c80da304bf26f66c62fbcb65667524ec


Please let me know if this works for you.

photo
1

One other note, it will be necessary to include another date filter in the union sub-query and link this filter to the primary date range filter that your report needs.

photo
1

Well it does work but that's an ugly work around with an undesired effects. The labels are numbers instead of being Cost and Margin. Also is there a feature request to allow a user to change slice color?

photo
1

In this case it is a difficult work around, but In terms of report construction, the current pie chart design works in far more cases than having the metrics be individual columns. (Eg when you have sales by region, your regions will most likely be rows rather than columns)


The reason that I included the view level calculated fields was specifically so that the labels would be appropriate:


fd4fd53bccb13a512f65b7b0ec215112


You can adjust what order colors are assigned in all charts through the content settings (the chart builder will assign colors in the order specified in the "Colors"). There are currently enhancements to allow you to change this on a report by report basis which I will add your information to.


ce0322b2fe7e871f19c33aaf2f8f43f4

photo
1

Hi Chad,


Any luck getting the labels to work?


Regards,

Nathan

photo
1

I had left yesterday right after the image of the pie chart post and didn't get to try until this morning. I was able to get it to work. I had misread the create calculated fields as just returning the same number not actually typing in a string even though it's obvious now. So this is a fine workaround just not something I would have figured out on my own. Also thanks for showing me the Color settings. I had not been in the Content Settings before so there wee all sorts of nice defaults to set for our environment.

photo
1

Glad to hear you got it working. I'm going to switch this to answered, but let me know if you run into any issues or have any other questions.

photo
1

Nathan

On a Dashboard I was trying to link a drill down field to this report. I can do it successfully on other reports but this report doesn't list any options. I can link a user input filter but not a drill down. Is this not possible in a sub-query report?

photo
1

Hi Chad,


I am not sure if this is what you are referring to, but it is not possible to link a drill down through a sub-query. In the underlying SQL, the join will go from:


A_parent=A_parent to A_child = A_parent

photo
1

I'm not sure if that answers my question or not. It explains why I can't drill down on a sub query report but is that the same thing I'm trying to do on the dashboard? On a dashboard I'm trying to have a drill down on one report be a filter on this pie chart report. I have a line chart broken down by dates. So when I click on say November on my line chart I want it to filter my pie chart results to November so I can see our cost and margin in November.

photo