The issue here is you can't use Measure Values twice in the same view, using different measures. You essentially want the left axis to have Total Success and Total Processed, and the right to have percentages.
You'll likely either need to decide on only one or the other having multiple measures; or you can try for a workaround that might be possible since one numeric group is percentages (so will be <1) and the other, hopefully, large enough numbers that will make this work.
See the attached worksheet for this sheet. The first two (Area graph alone (#) and Line graph alone (%)) just show the 2 pieces alone of what I'm using in the final combined view. The large numbers are in the Area Graph, and are Sales and Profit. The percentages are in Line Graph, and are some made up numbers - all under 1, of course.
Combined View combines these two. The key here is:
- ALL of the numbers you want to see - large numbers and percentages - are in the Measure Values page.
- Measure Values is on both sides of the dual axis.
- Changing Marks to Multiple Mark Types, the original Measure Values is an area graph, version (2) is a line graph.
- In the area graph, the percentages are so small, they will barely show up at the bottom of the area chart, not enough to be noticeable. Nothing needs to be adjusted here.
- In the line chart, this is clearly not the case. Instead, fix the right axis to only go from 0 to 1. The non-percentage numbers then will not show up on the graph, unless they ever fall to 0.
Hope this works for what you're trying to do!
You have to be kidding me! I was so close and messed with similar iterations. Thank you so much as the work around you provided is what I was looking for. You rock!
This is brilliant! Much easier to implement than Custom SQL solutions I've used in the past (and one that I just suggested). I did some playing around and found that a minimum value for the Area Marks of 2000 was enough to keep the % lines from showing up at all, though that needed to be 2500 when I used Bar Marks. One advantage to this solution is that you have one color legend for both axes.
A couple of other caveats I discovered: The high-value marks have to be Bars or Area marks, if they are another Mark Type then there will be marks drawn for the % measures - along the 0 line for. Conversely, the % marks have to be Lines or Circle/Square/Shape marks, if they are something else then there will be marks for the high-value measures.
1 of 1 people found this helpful
Patience isn't a virtue of mine.
Just applied a simple IF statement and it should be a good work around (for now)
if sum([Total Success]) <= 100 then 101
else sum([Total Success])
Ha - well you did wait 9 minutes Actually, your solution is pretty great. The main issue with my original fix is that is a workaround, so needs new workarounds for any other problems, as you found.
Here's one other option that could work: increase all the values of those variables by a factor of 1000, and then adjust the axes so it looks like you didn't. Make copies of each area graph variables that are the variables*1000, so sum([TotalSuccess])*1000 - plus a caveat IF/THEN to make sure 0's show up as 1000's. So now you should have the same pattern as you would but the axis will show millions instead of thousands. You can fix that in the axis formatting. Change the number format to use the units of Millions, and add the suffix "K". Then go into custom format and remove the "M" part of the formatting code (on my test it looked like #,##0,,"K";-#,##0,,"K"), and it'll look like thousands when it's really millions. Another workaround, though a little more work, and different potential issues long term.
One other note is, if you put the original variable in the Level of Detail, you can use those in the tool tip instead of the modified variable. Like the modification was never done at all!
On a similar note with dual axis formatting. How do I get one side to show numbers and the other percentage? When using the format from the axis drop down it inherently formats both axes.
I put these back to single axis just to reference what I'm talking about. When I try to change just the line chart to percentages it changes the top numeric values as well.
I went so far as making the measures as a percentage but that still didn't work. Any ideas?
Shoot. I don't know if that can be changed, since (as you found out) all of the Measure Values axes need the same scale format.
The best I can offer is either leave as is (the percentage axis can be formatted as 1.0, 0.9, 0.8, etc., and most will understand). You can also label the ends of the lines with percentages to make things clearer.
Two more ideas take advantage of the fact that the percentage axis will be fixed, from 0-100%. In both these, I hid the header on the percentage axis.
- You can create a "fake axis" with the percentages you need, and combine them in a dashboard. I did this in the attached using the average of Number of Records - a nice way to get 1. With a little manipulation, and maybe some blank objects, you can get this to look like it's the original axis you intended. (Tab New Combined View With Fake Axis)
- Add a reference line at each percentage you want labeled. Make them a constant value (0.8), with a custom label (80%), and no line. Format so it's vertically centered. Though I noticed 2 issues with this - it will also put these on the numeric axis so they will stack on the bottom left corner; and this will adjust this axis to start below 0 (which you can change manually, but this isn't ideal). (Tab New Combined View With Reference Lines)
Maybe someone else has an idea?
Digging around for some weird solutions and stumbled across this thread.
Daniel Vincent (I would @tag you but there are 3 Daniel Vincents)
How do I get one side to show numbers and the other percentage?
- Dual axis your measures
- Use quick table calc precentile
- or "RANK_PERCENTILE([Measure])" = your calc field
- Compute using Table Across
I know this is a late response, but I want others to be able to find the solution if they stumble upon this like me.
Senior Business Consultant
Can you please tell me how you created combined view sheet? I am unable to understand how one measure value is only affecting line graphs while other measure value in marks is affecting area graph. I tried to replicate your solution but it is affecting all my graphs. Please elaborate. Thanks.