Adding a percentage of two measures on a record?

#1



Hello,


I’m trying to figure out how to display a percentage of two values shown in a bar chart for a person/MSR. For example, my photo shows the first MSR with 1 EMAILCOLLECTED and 2 TOTALNEWACCCOUNTS so she has 50% rate.




  1. I’d like the value to show up in the lighter blue / EMAILCOLLECTED bar for each MSR.
  2. I’d like to know if there is a way for Dundas to figure this out. Currently that is calculated with my manual SQL (so it's available as a measure but I still cannot figure out how to insert it on top of the lighter blue bar), but I expect there is a better way.




Suggestions appreciated.



Image title





#2

This should be easy to do using a formula measure to calculate the percentage (to avoid the manual SQL), hidding that formula measure series under that formula series propeties but still referencing it under the emailcollected series data label text property using text placeholders. You can read more about text placeholder under tip#2 in the tips and tricks forum.

#3

Hi Ariel,

Thanks so much for the tips.

I'm pretty close to having this work for me, but my percentages are off. I am still using my percentage value that is calculated in my MANUAL SQL since I need the manual sql to obtain the data anyway. I've double checked my calculations when I run the SQL in my sql developer and for the set below they all come out either 0%, 50% or 100% so I suspect there is something going on in my metric set that is grouping them or summing them.


Any suggestions on how to figure out where it is going wrong?


Image title


#4

If you are still using the measure from your manual select, the only change should have been the addition of the percentage label. Did you make any other changes? It looks like you didn't have this issue in the original image you posted.

#5

I ended up having to recreate the entire dashboard due to a missing template, but I’m using the same data cube.


I rearranged the visualization a bit, but it’s essentially the same data. I’m still getting incorrect values on my PERCENTAGE even though my query returns the right amount so I’m guessing something in Dundas must be grouping or summing it.


I’ve included screenshots of my new dashboard with the incorrect values as well as the data from my query. Names have been cropped since they are not relevant and I don’t want to expose that data. I also include a copy of the Data Label setting. I don’t think I’m doing anything weird there since I really don’t know how to do much yet….




Image titleImage title




#6

The way you are presenting the data performs an aggregation of the emails collected in some cases. Since you are presenting the percentages on that, this also aggregates your calculated percentages. I think the issue here is that the aggregation doesn't always (though sometimes it does) match your expectation when calculating the percentage.


Looking at the data example you showed and the data visualization, it makes sense. If you are showing two data points, each of which was calculated to be 100%, you will get 200%.


I think the best course of action for you would be to ditch the manual SQL calculation and perform that calculation directly on the metric set, where the aggregation would be consistent. Coincidentally, I recently updated the relevant article with a similar example that should make it easy to apply.

#7

I found that if I changed the “Level” on the hierarchy to DAY, then the percentages are correct, is the aggregation when looking at month. I don't want to have to limit the view to only days, but that seems to be my solution right now. I'd really like it to work crrectly when we drill up to the month level.


I’ve tried to use a formula, but I’m not getting the results I think I should. My screenshot shows the values pulled from the database, by day. The highlighted yellow PERCENTAGE is my manual sql. The following columns are my attempts at formulas.


It looks like the “EMAILCOLLECTED PERCENTTOTAL Expression” is nearly correct and might only need to be multiplied by 100, but that seems off too.


Image title


#8

If you want it to work on the month as well, you will have to recalculate the percentages for the month level as well. Which is exactly what the formula measure does.


The PERCENTTOTAL expression defaults to return as a decimal number, which means you have to change the formatting to show it in the percentages you are used to (or multiply by 100). I think the article I mentioned included that in the example.

I also suspect that the Excell file you are showing is rounding the numbers, otherwise it's simply incorrect in line 4 that should be 0.13.

#9

Image title
s9HcTq5NQXq6wMoOrlwh_perecentage.jpg1249×694

Image title
yeDuRxyaTP25UEPPxS1c_perecentageByMonth.jpg1279×753

I’m still having trouble with the percentages. I’m not sure why this is so difficult for me to get to work as I would expect. Can anyone offer helpful insight as to how to fix this?


I have two hierarchies: name and a date field (a date hierarchy with day, month, year)

I have two measures: count of emails and count total


Percentage should be the number of emails divided by count total for that person for that day. As you can see in the screenshots the percentages are not correct.



#10

Image title
Iu2oo9jZQ2KQDEDJ3izI_formula_perecentage.jpg1038×800

The values shown above are based on a formula. I am not using SQL to get the percentage at this point.



#11

The PercentTotal formula returns the percentage of each value in the input series out of the total sum of the values in the same series. In terms of Excel, it will look something like =E5/SUM(E:E). If I understand your situation correctly, it would be more fitting to not use a built-in formula. The formula measure expression would look like a simple mathematical division of the two measures:

Image title

You can then edit this measure and change the Format to present the result as a percentage:

Image title


In the image below, the top left is simply the imported Excel data.

The top right is the same table with the new formula measure.

The bottom is a copy that was revisualized into a bar chart. The formula measure was hidden and used only for the percentage labels.

Image title
xUEkMFCoQGqRa9k8zD95_3.png1020×710


It also works with drilling up/down and collapsing/expanding the hierarchies.

#12

Support helped me out with this one; it turns out I waslooking to do a simple formula, not PERCENTTOTAL.