Hi,
I am trying to create a stacked bar chart that shows the percentage split of each of the measures that it consists of. The total value should be on the Y axis and the percentages should be within the sections of the stacked bar chart which in total should add up to 100%. Similar to the format below. Is this possible within Dundas?
Thanks!
Absolutely possible.
Create a stacked bar chart with the measure that you want, then add the regions as the columns, and the dates as the rows.
The built in percentage labels will only work for the 100% stacked bars so you will need to create a formula measure. Create a formula measure with a formula like PERCENTTOTAL($<measureName>$, $<regionName>$). This will create a measure which is the percentages which we can use for the labels.
In the chart properties, click on this new measure and in the 'main' tab, uncheck 'visible'.
Go back to the chart properties, and click on the original measure, and go to text, data label settings and type '[' in the text box, then select the percentage measure name. Set the position to be 'inside'
To create the total at the top, create a new formula measure with a formula like SUM($<measureName$,<$DateName$). Go to the chart properties and click on that measure.
Set the 'chart type' to 'point', then go to the 'look' tab and set the 'marker height' and 'marker width' to 0.
Go to the 'text', 'data label settings' and type '[' in the text box, then select the Sum measure name. Set the position to be outside.
Hope this helps, let me know if you want me to clarify or add anything.
Thanks
Hi David,
Thanks for the quick reply! Definitely a step closer to where I was before. I should have sent you an actual example of what I am working on and not something I found online. As you can see below I have 2 measures - Landfill and Recycling split out by month. I would like to see % of Landfill vs Recycling for every month - so every month adds up to 100%.
What I've done now gives me the recycling rates by month over the course of the year and doesn't compare it to the landfill rates. Could you give me some guidance on how I can change this?
Furthermore I am unable to turn the value by 90 degrees or move them inside the bars (when I click inside they just completely disspear, even when i change the color).
Thanks!
Hi Claudia
If you have 2 measures, then PERCENTTOTAL won't work for you, and you will need to calculate it manually.
I would have the two measures as you have them now, then create a new formula measure to get the total. This will be $Landfill$+$Recycled$. This measure will be your total, with display settings as above.
To get the percentages, I would create two new measures. One is $Landfill$ / $<totalMeasure>$ and the other will be $Recycled$ / $<totalMeasure>$. Set these to not 'visible', and use them in the data labels for the landfill and recycled measures, as I wrote in my earlier response.
If you want to display them as percentages, you could add * 100 to the formulas to multiply be 100, then click on the pencil next to the formula and set the format to N0 and put a % symbol 'after'.
Seems like you have kept '[' in the label text. I meant that if you type it, you can get the other measures appearing to select from. So the dataLabel settings text for the landfill series will be [<landfill formula measure>]
There is a 'rotation' setting for the data label settings, just under the 'placement' and 'text' settings. Set the rotation to 'none' and they will not be rotated. You can also set whether they go 'inside' or 'outside'. I suggest the measure percentage labels are 'inside' and the total is 'outside.
It could be that they are not appearing because the bar is too small to show them. You could try expanding the chart size, reducing the number of bars shown or reducing the label font size and margin to see if this helps.
Fanstastic! Thank you very much. Only last questions I have are:
1) Now the legend shows all the different measures that I've created (see below). How can I get it to only show Landfill and Recycled?
2) How do I add the % sign inside the barcharts next to the values and change it to include no decimal points.
Thanks so much for your help!
Claudia
1. In chart properties, for each measure there is the option near the bottom of the main tab of 'show in legend'. If you uncheck that, that measure won't display in the legend. You can also specify there if you want a different wording in the legend for that measure.
2. You can control how this appears by clicking on the pencil icon in the data analysis pane next to the measure that the label is showing. Set the 'Format' to N0, set the symbol to '%' and the position to 'after'.
Alternatively, you could do this in the text settings of the data label settings. Instead of [Landfill Expression] you could do [Landfill Expression:N0]%
3. From your screenshot, I would say you still need to set the marker height and width of the 'total' measure to 0 to hide that dot at the top of the bar. I would also set the 'outside alignment' of the 'total' measure data label settings to 'top'.
Good luck
Hi David,
Thanks!
It appears that my pane looks different to yours, when I click on Data Analysis Panel and then the pencil icon next to the measure I want to add the % to. There is no option to change the Format, Symbol or Symbol Placement nor can I change the name of the measure as this is greyed out. I know I was able to change it in the past as I changed it to the name it has now, but now it no longer is allowing me to. Any suggestions why this could be?
Thanks!
Claudia
Looks like the metric set has been checked in. Is this a separate metric set? If so, go to the metric set folder and check out that one.
