How can I show the % change in a column chart?

[ERing]

I have a report like below where I'm showing the period over period conversion rate of marketing leads to marketing contacts.

For each marketing channel (Televison, Facebook, Instagram, Radio, Email, SMS (Text Message) the first column is showing the conversion rate for the previous period and the second column is showing the conversion rate for the selected period.

My requirement is to show the % change from the first period to the second period. The percent change should be calculated as (Conversion Ratio - Conversion Ratio in Previous Period) / (Conversion Ratio in Previous Period).

Is it possible for me to somehow show this in the column chart? It doesn't neccissarily have to look like cards, just some way to indicate the value.

I'm just looking to avoid having to placeseperate card visuals as this can sometimes become a problem with scrolling or page zoom.

Power BI SAMPLE FILE 

[OwenAuger · Accepted solution]

Hi @ERing 

One way to handle this is to use a Line and clustered column chart.

Using this method, the line itself is hidden but the data labels are set to the % change in Conversion ratio.

I have attached example I mocked up using your PBIX.

1. Create measure Conversion ratio period-on-period % change

Conversion ratio period-on-period % change = 
VAR CurrentConversionRatio =
    [Conversion ratio (%)]
VAR PreviousConversionRatio =
    [Conversion ratio in previous period (%)]
RETURN
    DIVIDE (
        CurrentConversionRatio - PreviousConversionRatio,
        PreviousConversionRatio
    )

2. Create a measure defining where the labels should be positioned. I created two versions:

Line position above = 
VAR Scale = 1.2
VAR CurrentConversionRatio = [Conversion ratio (%)]
VAR PreviousConversionRatio =
    [Conversion ratio in previous period (%)]
VAR Result =
    IF (
        NOT ISBLANK ( CurrentConversionRatio )
            || NOT ISBLANK ( PreviousConversionRatio ),
        CALCULATE (
            MAXX (
                VALUES ( Data[Marketing Source] ),
                MAX (
                    [Conversion ratio (%)],
                    [Conversion ratio in previous period (%)]
                )
            ),
            ALLSELECTED ( )
        )
            * Scale
    )
RETURN
    Result

Line position below = 
VAR Position = - 0.05
VAR CurrentConversionRatio = [Conversion ratio (%)]
VAR PreviousConversionRatio =
    [Conversion ratio in previous period (%)]
VAR Result =
    IF (
        NOT ISBLANK ( CurrentConversionRatio )
            || NOT ISBLANK ( PreviousConversionRatio ),
        Position
    )
RETURN
    Result

3. Change the chart type to Line and clustered column

4. Add either Line position above or Line position below as the Line y-axis measure.

5. Change the data labels for the line to Conversion ratio period-on-period % change.

6. Create a measure returning the colour of the labels.

Label colour = 
IF (
    [Conversion ratio (%)]>=[Conversion ratio in previous period (%)],
    "#6ADA4F",
    "#D64550"
)

7. Set the line data label colour to this measure.

8. Set the line colour to white and turn off visibility of the line. Also rename the line to " " in the field well. (This ensures the line doesn't appear in the Legend).

You could do something very similar using visual calculations I'm sure 🙂

Note: The formatting settings in Power BI Desktop were a bit "fiddly" and sometimes the line data label colour was applied to the bars, but by changing it back-and-forth it eventually worked.

Is this the sort of thing you're looking for?

[OwenAuger · Accepted solution]

Hi again @ERing ,

With multiple charts, it is still possible to use the same set of measures on each chart using the method I posted earlier. However, rather than using a slicer to select the Conversion Ratio, you could instead apply a different visual-level filter to each chart (along with the Region filter).

I have updated your v3 PBIX, removing any measures not required for these visuals.

I have assumed a single region will be selected for each visual in the Chart Title measure (I noticed that one of your visuals had West/South both filtered which I assume was an accident).

The full set of measures is as follows, with the _Chart Measures display folder used directly within the visuals.

Taking the Leads to Contacts SOUTH REGION visual as an example, the filters applied are shown below:

Once you have created one visual, you can copy/paste and adjust the filters with no other changes.

I'm assuming the legends all showing "Conversion Ratio" rather than the specific measure name isn't an issue, since it's captured in the chart title.

Would this work for you?

[OwenAuger · Accepted solution]

@ERing if the X-axis field is adjustable by a field parameter, I would recommend a visual calc for the line rather than a measure.

I've attached an example of one chart with the line defined by this visual calc (renamed to " " to blank out the name in the legend)

Line Value = 
MAXX ( ROWS, MAX ( [Conversion Ratio % Prior Period], [Conversion Ratio %] )  ) * 1.2

For the X-axis, I created a dummy copy of the original axis field in order to create a field parameter with two options.

After creating the visual calc and placing it in the Line y-axis, the other formatting options and data label can be set up in the same way as they were for a measure.

Visual calculations are still in preview but this would be the most painless option I think. Would this work for you?

[ERing]

@OwenAuger I was presented another requirement which I can partially solve, but wondering if you have any input on where I'm struggling.

There have been several columns added to my data and my end users are requesting a dynamic way to change the x-axis on these conversion charts. The columns which have been added are Device OS, Marketing Agency, and Marketing Source Group.

I am able to handle changing the x-axis using a parameter in the x-axis feild well, but this presents a problem with the [Conversion Ratio % Period/Period Change Line Above] measure because the measure specifcally references Data[Marketing Source], which results in the Period/Period % values not displaying above the columns.

Do you think it would be possible somehow to dynamically change that part of the measure so that it references the slicer selection (Marketing Source, Device OS, Marketing Agency, Marketing Group)?

I was thinking SELECTEDVALUE may work, but it seems SELECTEDVALUE only works to select values from a single column where in my case I'm trying to change the colmun being referenced.

I was also thinking maybe a discconected table may allow this to work, but I'm not sure.

Any suggestions?

Example 

Conversion Ratio % Period/Period Change Line Above = 
VAR Scale = 1.2
VAR CurrentConversionRatio = [Conversion Ratio %]
VAR PreviousConversionRatio = [Conversion Ratio % Prior Period]
VAR Result =
    IF (
        NOT ISBLANK ( CurrentConversionRatio )
            || NOT ISBLANK ( PreviousConversionRatio ),
        CALCULATE (
            MAXX (
                VALUES ( Data[Marketing Source] ),
                MAX (
                    [Conversion Ratio %],
                    [Conversion Ratio % Prior Period]
                )
            ),
            ALLSELECTED ( )
        )
            * Scale
    )
RETURN
    Result

[OwenAuger · Accepted solution]

@ERing if the X-axis field is adjustable by a field parameter, I would recommend a visual calc for the line rather than a measure.

I've attached an example of one chart with the line defined by this visual calc (renamed to " " to blank out the name in the legend)

Line Value = 
MAXX ( ROWS, MAX ( [Conversion Ratio % Prior Period], [Conversion Ratio %] )  ) * 1.2

For the X-axis, I created a dummy copy of the original axis field in order to create a field parameter with two options.

After creating the visual calc and placing it in the Line y-axis, the other formatting options and data label can be set up in the same way as they were for a measure.

Visual calculations are still in preview but this would be the most painless option I think. Would this work for you?

[ERing]

Hi @OwenAuger  Thank you! This definitley works! I see how you added the added the dummy option to create the parameter, however I didn't intend for you to have to do that. I included in my previous post an updated example file additional columns that I intended to use on the x-axis. Apologies if that wasn't clear.

One other thing I'd like to be able to implement if possible is a way to limit the x-axis to a Top N based on the number of [Leads]. This is easy to do in the filter pane when the x-axis is constant, but I haven't found a way to do it when controlling the axis with a parameter. I'll keep searching for  solution on this but wanted to see if you have any suggestions.

Thanks again!

[OwenAuger]

@ERing Glad that method works, and apologies, I missed the Dropbox link in your previous post!

For TopN by an arbitrary field on the X-axis, I think you would have to write some conditional logic based on the selected field parameter value, as there is (currently) no way to apply a filter based on visual calculations.

Could you possibly re-share your latest pbix (the one from your previous post appears to have been deleted), otherwise I can create an example based on the most recent version I have.

[ERing]

@OwenAuger Thanks for looking into this. I've attached the latest example file. This has the visual calcuation line above for period over period % change and also uses the field parameter to control the x-axis.

The reason for needing the Top N is that my real data has many unique values in each of the columns used in the paramter and displayed on the x-axis. This sample data has at most 7 values (Marketing Source), but my real data has columns with 50 or more values. This doesn't display well in a chart. Ideally the solution for Top N would allow for a user input so they could easily switch between Top 3, Top 5, Top 10 etc.

Example PBI File 

[OwenAuger · Accepted solution]

@ERing Thanks for that!

After a bit of testing with a visual-level filter using a measure which ran into some technical issues, I would actually recommend a calculation group with a calculation item that blanks out all but the Top N values of the selected X-axis field.

1. First I created a numeric range parameter called Top N using the interface, with associated slicer and measure Top N Value.
2. Next I created a calculation group called Filter with the below calculation item Top N X-Axis by Leads. Since it has to cater for all possible X-axis field selections (and DAX doesn't allow tables to be returned by IF or SWITCH), it is quite longwinded!

VAR AxisSelection = IF ( COUNTROWS ( X_Axis_Parameter ) = 1, MAX ( X_Axis_Parameter[X_Axis_Parameter] ) )
VAR TopNSelection = [Top N Value]
VAR FilteredMeasure =
    SWITCH (
        AxisSelection,
        "Device OS",
            CALCULATE (
                SELECTEDMEASURE ( ),
                KEEPFILTERS (
                    TOPN (
                        TopNSelection,
                        CALCULATETABLE ( SUMMARIZECOLUMNS ( Data[Device OS], "@Leads", [Leads] ), ALLSELECTED ( ) ),
                        [@Leads]
                    )
                )
            ),
        "Marketing Agency",
            CALCULATE (
                SELECTEDMEASURE ( ),
                KEEPFILTERS (
                    TOPN (
                        TopNSelection,
                        CALCULATETABLE ( SUMMARIZECOLUMNS ( Data[Marketing Agency], "@Leads", [Leads] ), ALLSELECTED ( ) ),
                        [@Leads]
                    )
                )
            ),
        "Marketing Source",
            CALCULATE (
                SELECTEDMEASURE ( ),
                KEEPFILTERS (
                    TOPN (
                        TopNSelection,
                        CALCULATETABLE ( SUMMARIZECOLUMNS ( Data[Marketing Source], "@Leads", [Leads] ), ALLSELECTED ( ) ),
                        [@Leads]
                    )
                )
            ),
        "Marketing Source Group",
            CALCULATE (
                SELECTEDMEASURE ( ),
                KEEPFILTERS (
                    TOPN (
                        TopNSelection,
                        CALCULATETABLE ( SUMMARIZECOLUMNS ( Data[Marketing Source Group], "@Leads", [Leads] ), ALLSELECTED ( ) ),
                        [@Leads]
                    )
                )
            )
    )
RETURN
    FilteredMeasure

Applying this calculation item as a visual-level filter to each visual should work as intended. There are certainly alternative approaches to writing this but I believe the logic would need to be along these lines.

Does this work for you in your model?

[ERing]

Hi @OwenAuger  I apologize for the late reply. I've been under the weather and away from my computer for the last week or so.

I was just able to review this. This works great. Thank you very much!

[OwenAuger · Accepted solution]

Hi @ERing 

One way to handle this is to use a Line and clustered column chart.

Using this method, the line itself is hidden but the data labels are set to the % change in Conversion ratio.

I have attached example I mocked up using your PBIX.

1. Create measure Conversion ratio period-on-period % change

Conversion ratio period-on-period % change = 
VAR CurrentConversionRatio =
    [Conversion ratio (%)]
VAR PreviousConversionRatio =
    [Conversion ratio in previous period (%)]
RETURN
    DIVIDE (
        CurrentConversionRatio - PreviousConversionRatio,
        PreviousConversionRatio
    )

2. Create a measure defining where the labels should be positioned. I created two versions:

Line position above = 
VAR Scale = 1.2
VAR CurrentConversionRatio = [Conversion ratio (%)]
VAR PreviousConversionRatio =
    [Conversion ratio in previous period (%)]
VAR Result =
    IF (
        NOT ISBLANK ( CurrentConversionRatio )
            || NOT ISBLANK ( PreviousConversionRatio ),
        CALCULATE (
            MAXX (
                VALUES ( Data[Marketing Source] ),
                MAX (
                    [Conversion ratio (%)],
                    [Conversion ratio in previous period (%)]
                )
            ),
            ALLSELECTED ( )
        )
            * Scale
    )
RETURN
    Result

Line position below = 
VAR Position = - 0.05
VAR CurrentConversionRatio = [Conversion ratio (%)]
VAR PreviousConversionRatio =
    [Conversion ratio in previous period (%)]
VAR Result =
    IF (
        NOT ISBLANK ( CurrentConversionRatio )
            || NOT ISBLANK ( PreviousConversionRatio ),
        Position
    )
RETURN
    Result

3. Change the chart type to Line and clustered column

4. Add either Line position above or Line position below as the Line y-axis measure.

5. Change the data labels for the line to Conversion ratio period-on-period % change.

6. Create a measure returning the colour of the labels.

Label colour = 
IF (
    [Conversion ratio (%)]>=[Conversion ratio in previous period (%)],
    "#6ADA4F",
    "#D64550"
)

7. Set the line data label colour to this measure.

8. Set the line colour to white and turn off visibility of the line. Also rename the line to " " in the field well. (This ensures the line doesn't appear in the Legend).

You could do something very similar using visual calculations I'm sure 🙂

Note: The formatting settings in Power BI Desktop were a bit "fiddly" and sometimes the line data label colour was applied to the bars, but by changing it back-and-forth it eventually worked.

Is this the sort of thing you're looking for?

[ERing]

@OwenAuger This is fantastic. Exactly what I was looking for. Thank you very much.

One other question - Do you think it would be possible to conditional format the columns so that columns for "Conversion ratio in previous period" are gray and "Conversion Ratio" columns are green if greater than the previous period and red if lower than the previous period?

This isn't a must have, but a nice to have if possible.

[OwenAuger]

@ERing  Glad that's what you were looking for!

Unfortunately the core visuals don't support conditional formatting per data point when multiple measures or a legend are added to the chart.

I did play around with this hack using the PBIP format to format the bars based on the Label Colour measure but, even with that method, the colour of the bars is determined once at the visual level, not per data point.

However, I'm sure it would be possible to set this up with Deneb or maybe another custom visual.

[ERing]

@OwenAuger I'm not familiar with Deneb but I'll look into it. Thanks for the suggestion.

I came up with another question and would like your opinion if possible.

The actual report I'm developing will need to show multiple conversion rates. For example I'll need to show charts for below (and likely many others).

1. Lead to Contact conversion (already in the first sample file)
2. Contact to Appointment conversion

3. Appointment to Sale conversion

After reviewing the soultion you provided for displaying the Period over Period change % in the chart, I'm wondering if it would be possible to write a single measure for "Line position above" and or "Line position below" and a single measure for "Label Colour" that accounts for the conversion rate (Lead to contact, contact to appointment, appointment to sale) being shown in that specific chart?

Given the various conversion rates I'll need to show, I'm wondering if its possible to have a single line position measure and a single label colour measure rather than a line position and label colour measure for each conversion rate.

I was thinking the SWITCH function may be able to handle this, but I'm not sure how to write the measure and also not sure SWITCH is possible because I don't see how the measure would be able to evaluate/determine which conversion rate is being displayed in the chart ( and thus which specific "period-over-period" measure to display.

Maybe there is another way? Maybe it's not worth the trouble and I should write measures specific to each conversion rate?

I've provided an updated sample file here with measures for "contacts converted to appointments" should you have any suggestions/solution. My new chart will need to display [_Contacts to appointment conversion ratio in previous period (%)] and [Contacts to appointment conversion ratio (%)] along with the [_Contacts to appointment conversion ratio period-on-period % change] using the [Line position above] or [Line position below] and [Label colour] method you provided.

I can certainly follow the steps you've already provided to create the new chart, but I'm really more interested in any possible solution for writing a single measure for line position and label colour that captures both "Lead to contact conversion" and "Contact to appointment conversion".

I understand if it's too much effort to determine a solution. You've already been very helpful and I can certainly use individual measures to accomplish what I need.

Updated Sample File 

[OwenAuger]

@ERing based on those requirements, yes you can still measures used in the chart that don't change that SWITCH as required based on the selection of Conversion Ratio.

I've updated the PBIX with an example of how you could set it up.

The Conversion ratio (%) measure is the only one requiring SWITCH. The measures displayed in the chart all depend on this measure directly or indirectly.

Rough diagram:

I added a disconnected table Conversion Ratio which is used to select the conversion ratio to display.

The measuure Conversion ratio (%) then becomes:

Conversion ratio (%) = 
SWITCH (
    SELECTEDVALUE ( 'Conversion Ratio'[Conversion Ratio] ),
    "Lead to Contact", [Conversion Ratio Lead to Contact (%)],
    "Contact to Appointment", [Conversion Ratio Contact to Appointment (%)],
    "Appointment to Sale", [Conversion Ratio Appointment to Sale (%)]
)

See PBIX for the other measures.

Is that the sort of thing you were wanting to do?

[ERing]

Hi @OwenAuger  The solution you provided is one I would personally take as it greatly reduces the number of visuals on the report page.

Unfortunatley the requirement for my actual report is to show a chart for each "level" of conversion and specific to each Region (new attribute/dimension I've added in the data). I plan on using visual level filters to handle the Region.

So I will end up with 3 charts for each "Level" of conversion. Leads to Contacts Conversion (3 regions), Contacts to Appointments Conversion (3 regions), and so on for each "level" of attribution. I expect my real report will have 5 or more "levels" of conversion.

Currently I'll need a [Line Above] and a [Label Colour] measure for specific to each "level" of conversion.

I was hoping there may be a solution that provides a single [Line Above] and single [Label Colour] measure.

The solution you provided in v2 using a slicer for [Conversion Ratio] certainly accomplishes this, however I'm not sure it will work with my requirement to have a visual for each chart.

I've included a v3 file which shows how my real report will look. I've only showin two regions for each of the 3 levels of conversion.  The real report will have 3 regions for each chart.

Bar Chart Period over Period Variance by Dimension Example V3 

[OwenAuger · Accepted solution]

Hi again @ERing ,

With multiple charts, it is still possible to use the same set of measures on each chart using the method I posted earlier. However, rather than using a slicer to select the Conversion Ratio, you could instead apply a different visual-level filter to each chart (along with the Region filter).

I have updated your v3 PBIX, removing any measures not required for these visuals.

I have assumed a single region will be selected for each visual in the Chart Title measure (I noticed that one of your visuals had West/South both filtered which I assume was an accident).

The full set of measures is as follows, with the _Chart Measures display folder used directly within the visuals.

Taking the Leads to Contacts SOUTH REGION visual as an example, the filters applied are shown below:

Once you have created one visual, you can copy/paste and adjust the filters with no other changes.

I'm assuming the legends all showing "Conversion Ratio" rather than the specific measure name isn't an issue, since it's captured in the chart title.

Would this work for you?

[ERing]

Hi @OwenAuger  This is fanastic. Exaclty what I was looking for!

There is one remaining issue that I haven't been able to solve.

The Period over Period % Change data labels in my actual report are not staying above the columns in the chart. You can also see the white line across some of the columns.

I've tried combinations of removing and adding the value labels from the primary and secondary y-axis, but that didn't solve it.

I'm a little stumpped on this becuase it works perfect in the example files and I've repeated everything from the example files to my real report. You did mention this was a little finnicky when you first posted the solution, but I've not been able to resolve this.

I can always use the [Line Below] measure, but I think my users would prefer the [Line Above]. 

Two examples below, each with a different date range. I erased the x-axis text labels from the image for confidentiality.

[OwenAuger]

@ERing hmm, can't be certain what's going on there but something seems to be wrong with the values returned by the line measure.

The "finnicky" issue I originally had was setting the data labels for the line. I had to repeatedly set the data labels for both bar measures and the line for some reason. However, I don't think I had any issue with the position of the line itself.

To troubleshoot, could you select More Options > Show as table on the visual to see what values are actually being plotted for the line (screenshow below)?

Also, you should hide the line with this setting:

Does any of that help debug?

[ERing]

@OwenAuger  I found the issue. I had the wrong field in the VALUES part of the [Line Above] measures. I corrected it and everything is working great now.

Thanks again for all your help on this!