14 Replies Latest reply on Sep 19, 2018 7:52 AM by Simon Runc Branched to a new discussion.

# How to calculate % Of Total sales made by each segment compared to the total sales.I want to create a calcualted field for getting that.So that for each segment I will be able to do separately.

How to calculate % Of Total sales made by each segment compared to the total sales.I want to create a calcualted field for getting that.So that for each segment I will be able to do separately.

• ###### 1. Re: How to calculate % Of Total sales made by each segment compared to the total sales.I want to create a calcualted field for getting that.So that for each segment I will be able to do separately.

hi Isa,

So I think what you are asking, and correct me if I'm wrong (certainly won't be the first time!!)...is that you want a % of Total, but for it to Filter and vizLoD independent.

One way is to use a fixed LoD

In the attached, I've created the following calculation

[Sales %age of Segment - LoD]

{FIXED [Segment]: SUM([Sales])}

/

{SUM([Sales])}

As as you'll see if you filter out one of the segments the other 2 still show %age of Total.

Hope that helps.

1 of 1 people found this helpful
• ###### 2. Re: How to calculate % Of Total sales made by each segment compared to the total sales.I want to create a calcualted field for getting that.So that for each segment I will be able to do separately.

Hi Simon,

Actually I want to show the sales percentage of each segment in donut chart compared to the original sales.THIS IS HOW IT SHOULD LOOK LIKE.Can you please help in that? • ###### 3. Re: How to calculate % Of Total sales made by each segment compared to the total sales.I want to create a calcualted field for getting that.So that for each segment I will be able to do separately.

I see...

So actually we can use the same basic method...there are a few ways to do this, but below is one!

As we actually want the bits (blue) of all the pies to add up to 100%, and so only the %age for each segment is shown in each chart, I've opted to create a calculated field (again a FIXED LoD) to act as the grey (rest of sales part), and then "stacked" these measures (using measure values/names).

So I have the original

[Sales %age of Segment - LoD]

{FIXED [Segment]: SUM([Sales])}

/

{SUM([Sales])}

which I'll use for the central labels

I've then created

[Sales Rest]

{SUM([Sales])}-{FIXED [Segment]: SUM([Sales])}

I then just stack this ([Sales Rest]) measure with SUM([Sales]) and each "full" donut will be 100% with the SUM([Sales]) the sales for each segment, and the  [Sales Rest] the remainder for each one.

I then arrange like this (using dual axis on MIN(1) to create the donut) Let me know if that doesn't do the trick, or make sense!

3 of 3 people found this helpful
• ###### 4. Re: How to calculate % Of Total sales made by each segment compared to the total sales.I want to create a calcualted field for getting that.So that for each segment I will be able to do separately.

Hi Simon,

That was exactly I wanted..thanks a lot

• ###### 5. Re: How to calculate % Of Total sales made by each segment compared to the total sales.I want to create a calcualted field for getting that.So that for each segment I will be able to do separately.

Hi Simon,

Is there any way to color each segment?(can we replace blue color on each segment with any other colors representing each segment?).if its not there also I am happy,but I was wondering if we could do that?

• ###### 6. Re: How to calculate % Of Total sales made by each segment compared to the total sales.I want to create a calcualted field for getting that.So that for each segment I will be able to do separately.

So using the same method, the easiest way is to also add Segment to the colour tile (in Tableau we are able to add multiple colouring blue pills)

This leads to a colour being assigned for each Segment/Measure_Name combination. We can then set all the 'Rest Sales' ones to grey, and set the 'Sales' colours for each segment This does require a bit of manual colour set up...and if you get a new segment in your data (following a data update) you'll need to manually go back and re-set the new 'Sales Rest' segment to grey (as Tableau will not pick this up automatically as part of the "things we want grey", as it would if we just used the single colouring version...where there is only Rest or Sales, and so all Rest would be grey.

Hope that does the trick!

1 of 1 people found this helpful
• ###### 7. Re: How to calculate % Of Total sales made by each segment compared to the total sales.I want to create a calcualted field for getting that.So that for each segment I will be able to do separately.

Hi Simon,

When I add segment to color ,then it colors the complete ring outside..I am not able to add segment and sales together in color shelf.How can I achieve that?Can you please help me on that?

• ###### 8. Re: How to calculate % Of Total sales made by each segment compared to the total sales.I want to create a calcualted field for getting that.So that for each segment I will be able to do separately.

Yes I got it..Now i could add both sales and segment to color.Thanks a lot for your help.Really appriciate .

• ###### 9. Re: How to calculate % Of Total sales made by each segment compared to the total sales.I want to create a calcualted field for getting that.So that for each segment I will be able to do separately.

Hi Simon,

Again one more question.Can we color the label on each segment with the same color what we have given for each segment.For example I have labels above each colored segment like the below picture.(In the picture segments are not colored,but I have colored in mine).Can we add the same colors to the label as well.How can we achieve that?Sorry for asking too much questions..but wanted to explore more and more on

tableau features.

• ###### 10. Re: How to calculate % Of Total sales made by each segment compared to the total sales.I want to create a calcualted field for getting that.So that for each segment I will be able to do separately.

No probs on all the questions...an inquisitive mind is what it's all about!

So much as it pains me to say...I think this is where we come to a halt! If we want the text to dynamically colour we have 2 options. One is to use a Text Mark, and add colour to the colour tile. The other is the "Match Mark Colour" on labels We can't use option 1, as we need a Circle (be that a Circle Mark, Circle Shape or Pie) to "hollow" out the donut. And with option 2 this happens I've even tried using a "custom shape" and tried multiple formats (.jpg, .bmp, .tiff) hoping that one wouldn't take the colour from Tableau, in which case it wouldn't show the colour, but would be there for the Label...but alas they all take colour.

The best I've come up with is to create the label from the main Pie, and then can use the "Match Mark Colour", using the MAX on measure name, so it only plots the coloured one, and not a grey version. I'll keep the grey-cells thinking about it, but don't think it's possible (unless we do something crazy with polygons (eg Creating Coxcomb Charts in Tableau « Bora Beran)...which by the time you've done the work for that, you may as well build them individually (in a sheet per segment) and then put them together in a container on a dashboard.

1 of 1 people found this helpful
• ###### 11. Re: How to calculate % Of Total sales made by each segment compared to the total sales.I want to create a calcualted field for getting that.So that for each segment I will be able to do separately.

Again Thanks a Lot ...That served the purpose....

• ###### 12. Re: How to calculate % Of Total sales made by each segment compared to the total sales.I want to create a calcualted field for getting that.So that for each segment I will be able to do separately.

Ho can I create a chart like the picture below.I need to calculate the daily average sales of 2015 and daily average sales of 2016 and it should be represented as lines shown in the picture and Sales2015 and sales 2016 as 2 bars .But all in same view.Can you please help me in that. Edit SimonRunc: Branched to new question Show Total Sales and Average Daily Sales by Month/Year on same chart

• ###### 13. Re: How to calculate % Of Total sales made by each segment compared to the total sales.I want to create a calcualted field for getting that.So that for each segment I will be able to do separately.

Hi Simon, I am trying to do the same thing but with all the variables aggregate. So when I am having all the filters selected I am having only one circle with 100% or with the sum of the percentges of each cluster selected. I was trying to work on your model, however when I remove the column (segment), the percentage fit, but the represention of the percentage on the circle does not. basically the colors stay the same no matter which percentage is. Do you know how to solve this problem?

Thanks a lot in advance,

s

• ###### 14. Re: How to calculate % Of Total sales made by each segment compared to the total sales.I want to create a calcualted field for getting that.So that for each segment I will be able to do separately.

hi Simone,

I think this might be due to the Share of Sales being worked up from a FIXED LoD which get computed before any filters are applied. We can however bump the filter up the calculation pipeline by making it a context filter (select the filter and choose "add to context"). If that's not the problem then can you post a workbook, showing what you want. This was set up for this specific question so the formulas (particularly the LoD) is based on the Segment being in the VizLoD, so may need a bit of tweak if you want it displayed without.

Thanks

1 of 1 people found this helpful