How to create a dynamic Range for bar chart

[Arthur_Fong]

Hi all,

I have a question here:

When I added a reference line to a bar chart, if the value of the reference line is too high, it will shows as 1.

Refer screenshot below, Chart on the left.

I managed to create a customized range by adding a minimum value into the range, chart on the right.

However, this range seems weird, normal range should looks like 0 to 600k, a rounded value instead of showing as decimal point.

Any idea how to create a dynamic range for this?

For example, when CHINA is selected, range should be 0 to 600k.

Subsequently when NEPAL is selected, range should be 0 to 600.

Refer sample qvf attached.

Thanks and regards,
Arthur Fong

[Arthur_Fong · Accepted solution]

Create a variable(vRangeCeil) to store the equivalent length for the line value:
='1'&repeat(0,len(sum(Plan))-1)

This will return the number of tenths for each selection.
eg: for CHINA, plan value= 6000. This will return 1000.
       for NEPAL, plan value = 600. This will return 100.

On the range value, add in the variable created into the expression:
ceil(sum(Plan),$(vRangeCeil))