Interesting question and not certain I fully understood
see the attached - it is a hack
I created a fixed total sales calculation just to have something greater than your data
then plotted with dual axis and sync'd the axes
the hid the header for the top axis (and als mase it a lin graph that is out of the view
this is what results
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
Axis Question_v10.5.twbx 350.0 KB
I would go with automatic, and handle the outliers specially.
If you have max value = 100, automatic would display it the way you want. Ditto anything up to 10,000.
Here is what I would do though. I would have a [Display Value] in addition to [Value]. You'd put [Value] on the details shelf so you can use it in labels and tooltips. You would use [Display Value] to create the size of the bars. And it would simply be like this:
IF SUM([Value]) > 10000 then 10000 else SUM([Value]) END
Now the bar's max size will be cut off at 10,000, but your actual Value numbers would be preserved for other display components.
Joe - Thank you very much for replying! I think your solution would work if I was using a specific measure, but I have measure values on the shelf. I wasn't able to get this method to work.
If possible, I would pivot your data to make it a vertical structure, with the measures of interested stored in a column (MeasureCD + MeasureDSC) and the values (Numerator/Denominator) stored accordingly.
Then you can normalize the values as needed (while keeping the axis fixed) by using a WINDOW_MAX() function (to compare against 5500 and/or 10000 and making others proportional to the max.
Hi Bryce - Thank you. Creative solution, but unfortunately it wont work for my use case. I am trying to match the customers specifications exactly, therefore, the bars have to be in a horizontal orientation.
I wish there was a built in way to control axis ranges dynamically!
Deborah Diesel wrote:
... I think your solution would work if I was using a specific measure, but I have measure values on the shelf. I wasn't able to get this method to work.
Do the measures change? Or are the same x-many measures always on the sheet?
If the latter, you can still make this work. You'll just have to have separate [Display (measurename)] calcs for each measure.
Heh, sorry - I should have been more clear. I was specifically speaking about the data structure, not the graph itself.
Category Measure MeasureValue
Office Supplies Sales $$$
Furniture Profit $$$
This allows a fair bit more flexibility when doing something such as normalization.