Add a constant line for 'Line & Clustered column chart' = Combo chart

[Anonymous]

I am looking for a way to add a constant line - 5% dotted line in my combo chart ' Line & Clustered column chart' 

Combo chart configuration. 

Chart would like this:-

Currently the analytics pane for combo chart  doesn't contain a  "+ Constant"  to let me draw a 5% constant line. 

However, for other visuals - like regular bar chart, it comes with 'Constant line' feature. 

Which gives me the result I need on the 5% dotted constant line

How to achive a constant line for ComboChart above?

[amitchandak]

@Anonymous , check if you can create a measure and add a like

measure =.5

add that in the combo visual and change line style

[mmaepa1]

This is awesome, It worked perfectly Thanks

[Anonymous]

Had tried the measure approach as well.

Since the primary Y axis is %, and 2nd Axis is number. (2 different unit of measurements)

If I add a "measure 5" to my combo line chart. it will become like this:- which is not what I want.

Black bars = 5%

[DataZoe]

@akiko_lim You should be able to move it to the line values:

[Anonymous]

Thanks DataZoe for your quick reply. very much appreciated. 

If I move the 5%line to line Values, my 2nd Y-Axis UoM (unit of measure) will not be correct. 

I am mixing Pecent data (5%) line with other # of Test (whole number) . My chart will not be correct. 

See below, 2000% 

[DataZoe]

@Anonymous oh you are right, I mis-read. I thought your %'s were on the line, not the columns. And you did such a good job explaining it with screenshots too -- my apologies! I'll give it some more thought, but I'm not sure how you would do this with the %'s on the bars. 

[fhill]

If your LINE Axis values are FIXED, you can calculate based on the position of the Bars, the correct Value of 5% to hard code (guessing around '5%Line = 80' or so based on the line axis) to fake this....  But if your Line Axis is variable based on the data selected, this work-around won't work.

OR...  do you REALLY want to SUM all values of the LINE, then divide by the number of days, then divid by .05 to Calculate the true 5% mark compared to the Top Line?  If there's possibily some true relation between the Bar and Lines?

FOrrest

[Anonymous]

In my case here, the 5% line is a "reference point" for the % values on Bar. - which is my primary Y-Axis. (left axis) 

5% line has nothing to do with the other lines being plotted on 2nd Y-Axis (right axis). 

[fhill]

I don't recall if you said your right Axis was fixed or variable?  If It's fixed, I created this measure  

     5%Line = 180

to approximate the 5% goal...  This only works if the right Axis (Line #'s) are fixed (not based on a filter or selection), or you want to re-adjust this measure every time you update the report?

If it's variable, I have another idea to Calcuate the difference betewen the MAX and MIN % values shown (to determine the scale of the bars), then use 5% of that difference to MULTIPLY by the lowest LINE Value....   I'll try this next if the above work-around doesn't help...

[Anonymous]

Hi fhill, 

thanks for your suggestion below. To create the calc line would work to provide "visual clues". However, the tooltip when you hover the cursor over it will not be accurate. Won't show 5% line. 

Alternatively,  to switch the data around, let the % be on the 2nd Y. Let me verify with product manager if this visual will be meaningful to user. 

Appreciate your inputs! it's been very helpful to have other folks comment to verify I didn't miss any features available with this native chart. 

[fhill]

You are right about the Tool Tip for sure.  If they decide they are OK it showing a fake value instead of '5%'.  Here's the math to figure out your calculated line regardless of the values for Bars or Lines.  ** HOWEVER, All Axis 'Start' values need to be set to 0! **

** P.S.  I do things very broken apart to show my thoughts / steps...  Feel Free to condense this into one Measure if wanted / needed. **  (Or can someone else do so for us?)

Max_Bar = CALCULATE( MAX('Table'[%]), ALLSELECTED() )

5%Bar = [Max_Bar] / 0.05

Max_LINE = CALCULATE(Max('Table'[Value]), ALLSELECTED() )

Max_Line / 5%_Bar = [Max_LINE] * ( 1 / 'Table'[5%Bar])

[Anonymous]

Thanks fhill for your sharing the DAX on your solution you proposed. Got it. Thanks again. 🙂 

[DataZoe]

@Anonymous You could create a measure:

Constant 5% = 0.05

then add that as an additional line in the combo chart with your existing % Outliers measure. You can then format it to be red and dashed.