Hi all,
I've been struggling to build a chart that has a date on the x-axis and KPI on the y-axis, with the markers for each KPI connected by a vertical line. I've been trying to achieve this using a line chart, deselecting the lines and keeping only the markers, and adding error bars, but I can't get it to work. I can't use shapes because I have a month slicer and when a user changes the month, the shapes would remain on the chart, so the solution must be dynamic. The KPI name panel on the left side of the screenshot doesn't necessarily have to be part of the chart as I can put that in a text box, but the user insists on having vertical lines that connect each KPI, and ideally each vertical line should have a different color for each month. I hope this makes sense; please let me know if you have any ideas. Thanks!
Honestly, I would try to get more info as to what insights they actually want to get from this visual. Usually if you are looking at numbers month over month, you want to be able to compare between months, but the visual elements here seem to only interfere with that kind of analysis.
That said, I was able to finagle the below visual. This is a regular line visual with lines turned off and error bars added for the vertical lines. The dynamic y-axis (moving KPI labels) is a separate visual.
There are no options to do dynamic coloring on the markers or error bars, so the best you can do is just set to all the same color. You can probably get exactly what you want with a custom visual usuing deneb/vega-lite, R, etc. but the complexity could get pretty high, especially if you need slicer interactability.
You didn't provide what your model looks like and I didn't put too much effort in terms of building up a good sample, as most of the complexity is in the visual settings. Here is the sample I used:
Sample
| KPI | Month | Value |
| KPI 1 | Month 1 | 0.86 |
| KPI 1 | Month 2 | 0.3 |
| KPI 1 | Month 3 | 0.4 |
| KPI 1 | Month 4 | 0.5 |
| KPI 2 | Month 1 | 0.59 |
| KPI 2 | Month 2 | 0.2 |
| KPI 2 | Month 3 | 0.3 |
| KPI 2 | Month 4 | 0.4 |
| KPI 3 | Month 1 | 0.72 |
| KPI 3 | Month 2 | 0.2 |
| KPI 3 | Month 3 | 0.3 |
| KPI 3 | Month 4 | 0.4 |
| KPI 4 | Month 1 | 0.65 |
| KPI 4 | Month 2 | 0.2 |
| KPI 4 | Month 3 | 0.3 |
| KPI 4 | Month 4 | 0.4 |
| KPI 5 | Month 1 | 0.91 |
| KPI 5 | Month 2 | 0.72 |
| KPI 5 | Month 3 | 0.85 |
| KPI 5 | Month 4 | 0.66 |
| KPI 6 | Month 1 | 0.68 |
| KPI 6 | Month 2 | 0.2 |
| KPI 6 | Month 3 | 0.3 |
| KPI 6 | Month 4 | 0.4 |
Steps I took to get the above visual.
1) The main trick to get the markers placed is using something like the following measure where each KPI % calc is dynamically stacked. With the below, the last KPI (KPI 6 with no filters) falls in the y-axis range of 0-1 (ie 0%-100%), second to last falls into 1-2, and so on. We're basically adding the KPI descending order to the KPI %.
KPI stack val =
IF(
ISINSCOPE( Sample[KPI] ),
ROWNUMBER( ORDERBY( Sample[KPI], DESC ) ) - 1
+ SUM( Sample[Value] )
)
2) Put Sample[Month], Sample[KPI], and the above measure into a line chart:
And perform some preliminary formatting: turn off legend, turn off lines, remove visual title, remove y- and x-axis titles, remove y-axis values, turn on markers, and finally do whatever configuration you want for your look (I set marker color to blue, made marker size a little larger at 6px, and changed color/size of x-axis values):
3) Add data labels. First, create your % measure and set the % formatting to what you want. For the above sample, this was just:
KPI % = SUM( Sample[Value] )
Then go back to the visual, turn on data labels, and switch in the new measure in the Value section. Also, in Options section of data labels, change position to Above and set the offsetts to 0. You can tweak other asthetics as needed - eg I set value color to black.
4) Set the y-axis range so that we have consistent relative placement of markers and ensure our dynamic y-axis stays aligned with the main visual. First, set up a measure for the max range:
KPI Count = COUNTROWS( VALUES( 'Sample'[KPI] ) )
Go to Formatting > Y-axis > range and set minimum to 0 and maximum to the measure:
5) At this point, we can set up the dynamic y-axis labels. Duplicate what we have for the visual so far, remove our [KPI stack val] measure from Y-axis, and replace with the following measure:
DynAxis =
IF(
CALCULATE( MIN( Sample[Month] ), ALLSELECTED( Sample ) )
= SELECTEDVALUE( Sample[Month] ),
[KPI stack val]
)
This is mostly doing the same as [KPI stack val], except we are only displaying the values for the first selected month. Decrease the new visual's width and move next to the main visual:
Next, drop in Sample[KPI] into the data label values (ie can get away with using implicit First KPI measure) and turn off markers and the x-axis values. Update the data label formatting to match what you want (I increased size to 18px). Important note: given the small width, you most likely have to turn off Format > Properties > Advanced options > Responsive for larger data labels to display correctly.
Two final format edits: i) set Format > Size and style > Padding for bottom to max at 20px to align visuals (add transparent x-axis values or title back in if more padding if needed), and ii) turn off tooltips (Format > Properties > Tooltips). The dynamic axis labels should be all set now:
6) Final step: add the vertical lines to the main visual. We can do this with Error bars (at bottom of Format > Visual pane). Set up the following measures before we start:
ErrMax = MAXX( ALLSELECTED( 'Sample'[KPI] ), [KPI stack val] )ErrMin = MINX( ALLSELECTED( 'Sample'[KPI] ), [KPI stack val] )
Now, place these into Error bars > Options > Upper bound / Lower bound. Tweak visuals to what is desired (I set bar color to blue and increased width to 2px) and that should be it:
Note: error bars display a bar for each data point. The above measures, with Relationship to measure set to absolute (default), give all the error bars the same dimensions on each x-axis point (top KPI marker to bottom KPI marker).
Honestly, I would try to get more info as to what insights they actually want to get from this visual. Usually if you are looking at numbers month over month, you want to be able to compare between months, but the visual elements here seem to only interfere with that kind of analysis.
That said, I was able to finagle the below visual. This is a regular line visual with lines turned off and error bars added for the vertical lines. The dynamic y-axis (moving KPI labels) is a separate visual.
There are no options to do dynamic coloring on the markers or error bars, so the best you can do is just set to all the same color. You can probably get exactly what you want with a custom visual usuing deneb/vega-lite, R, etc. but the complexity could get pretty high, especially if you need slicer interactability.
You didn't provide what your model looks like and I didn't put too much effort in terms of building up a good sample, as most of the complexity is in the visual settings. Here is the sample I used:
Sample
| KPI | Month | Value |
| KPI 1 | Month 1 | 0.86 |
| KPI 1 | Month 2 | 0.3 |
| KPI 1 | Month 3 | 0.4 |
| KPI 1 | Month 4 | 0.5 |
| KPI 2 | Month 1 | 0.59 |
| KPI 2 | Month 2 | 0.2 |
| KPI 2 | Month 3 | 0.3 |
| KPI 2 | Month 4 | 0.4 |
| KPI 3 | Month 1 | 0.72 |
| KPI 3 | Month 2 | 0.2 |
| KPI 3 | Month 3 | 0.3 |
| KPI 3 | Month 4 | 0.4 |
| KPI 4 | Month 1 | 0.65 |
| KPI 4 | Month 2 | 0.2 |
| KPI 4 | Month 3 | 0.3 |
| KPI 4 | Month 4 | 0.4 |
| KPI 5 | Month 1 | 0.91 |
| KPI 5 | Month 2 | 0.72 |
| KPI 5 | Month 3 | 0.85 |
| KPI 5 | Month 4 | 0.66 |
| KPI 6 | Month 1 | 0.68 |
| KPI 6 | Month 2 | 0.2 |
| KPI 6 | Month 3 | 0.3 |
| KPI 6 | Month 4 | 0.4 |
Steps I took to get the above visual.
1) The main trick to get the markers placed is using something like the following measure where each KPI % calc is dynamically stacked. With the below, the last KPI (KPI 6 with no filters) falls in the y-axis range of 0-1 (ie 0%-100%), second to last falls into 1-2, and so on. We're basically adding the KPI descending order to the KPI %.
KPI stack val =
IF(
ISINSCOPE( Sample[KPI] ),
ROWNUMBER( ORDERBY( Sample[KPI], DESC ) ) - 1
+ SUM( Sample[Value] )
)
2) Put Sample[Month], Sample[KPI], and the above measure into a line chart:
And perform some preliminary formatting: turn off legend, turn off lines, remove visual title, remove y- and x-axis titles, remove y-axis values, turn on markers, and finally do whatever configuration you want for your look (I set marker color to blue, made marker size a little larger at 6px, and changed color/size of x-axis values):
3) Add data labels. First, create your % measure and set the % formatting to what you want. For the above sample, this was just:
KPI % = SUM( Sample[Value] )
Then go back to the visual, turn on data labels, and switch in the new measure in the Value section. Also, in Options section of data labels, change position to Above and set the offsetts to 0. You can tweak other asthetics as needed - eg I set value color to black.
4) Set the y-axis range so that we have consistent relative placement of markers and ensure our dynamic y-axis stays aligned with the main visual. First, set up a measure for the max range:
KPI Count = COUNTROWS( VALUES( 'Sample'[KPI] ) )
Go to Formatting > Y-axis > range and set minimum to 0 and maximum to the measure:
5) At this point, we can set up the dynamic y-axis labels. Duplicate what we have for the visual so far, remove our [KPI stack val] measure from Y-axis, and replace with the following measure:
DynAxis =
IF(
CALCULATE( MIN( Sample[Month] ), ALLSELECTED( Sample ) )
= SELECTEDVALUE( Sample[Month] ),
[KPI stack val]
)
This is mostly doing the same as [KPI stack val], except we are only displaying the values for the first selected month. Decrease the new visual's width and move next to the main visual:
Next, drop in Sample[KPI] into the data label values (ie can get away with using implicit First KPI measure) and turn off markers and the x-axis values. Update the data label formatting to match what you want (I increased size to 18px). Important note: given the small width, you most likely have to turn off Format > Properties > Advanced options > Responsive for larger data labels to display correctly.
Two final format edits: i) set Format > Size and style > Padding for bottom to max at 20px to align visuals (add transparent x-axis values or title back in if more padding if needed), and ii) turn off tooltips (Format > Properties > Tooltips). The dynamic axis labels should be all set now:
6) Final step: add the vertical lines to the main visual. We can do this with Error bars (at bottom of Format > Visual pane). Set up the following measures before we start:
ErrMax = MAXX( ALLSELECTED( 'Sample'[KPI] ), [KPI stack val] )ErrMin = MINX( ALLSELECTED( 'Sample'[KPI] ), [KPI stack val] )
Now, place these into Error bars > Options > Upper bound / Lower bound. Tweak visuals to what is desired (I set bar color to blue and increased width to 2px) and that should be it:
Note: error bars display a bar for each data point. The above measures, with Relationship to measure set to absolute (default), give all the error bars the same dimensions on each x-axis point (top KPI marker to bottom KPI marker).
@dzounas5212 If you have an actual Date, so a numeric value for your x-axis, then you can use x-axis constant lines from the Analytics tab of the Visualizations pane. Unfortunately the x-axis must be set to Continuous and not Categorical.
I've tried to turn it off, but got the same result. I have version of BI Desktop 2.149.1252.0 64-bit
@dzounas5212 , I am thinking of a Stacked or Clustered Line visual with column should have value which is max. Put all other KPIS (as Measures on Line Axis) with Line Width 0 and Marker and Label enabled for line
Using column layout reduce the width
Again for column I am using transparency as 100% , but Border color
file is attached filter signature, check page 53
We are little bit closer, but now I'm struggling with the Column axis, as on some of the dates(waves in my case) it's not reaching the KPI values, even though it's max of 'base % measure', which the KPI's are using.
