How to include growth rate in Clustered Column Chart (Power BI)

[Anonymous]

Hello all,

Is there a way to include growth rate % in the Clustered Column Chart?

As you can see from the image:

I would like to show the growth rate% of multiple categories from 2017 to 2019.

Ideally is to have an indication of growth percentage between the columns.

I am fairly new in BI, appreciate if anyone can show me the ways of achieving this.

Many thanks!

[amitchandak]

@Anonymous , you can use combo visual. But multiple lines are not possible using the legend . you need to have multiple measures

https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-combo-chart

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))

Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[Anonymous]

Hi @amitchandak ,

Thank you for your reply!

I have created a seperate calender table and all the measures mentioned.

Not sure what to do the next with the clustered combo chart to show the growth like this:

If not, appreciate your patience to explain other ways of achieving this.

Thank you so much!

[Anonymous · Accepted solution]

Hi @Anonymous 

I build a sample table to have a test. Add a calculated column Year in this table.

Currently, we can only add a Tooltip and hover our mouse on the value to see it.

Build measures as below.

Growth Rate % = 
VAR _Thisyear =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        ALLEXCEPT ( 'Table', 'Table'[Category], 'Table'[Year] )
    )
VAR _Lastyear =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Category] = MAX ( 'Table'[Category] )
                && 'Table'[Year]
                    = MAX ( 'Table'[Year] ) - 1
        )
    )
VAR _Diff = _Thisyear - _Lastyear
VAR _Rate =
    DIVIDE ( _Diff, _Lastyear )
RETURN
   _Rate

Tooltip 1 = 
IF (
        [Growth Rate %] = BLANK (),
        BLANK (),
        IF (
            [Growth Rate %] > 0,
            "⇧" & ""
                & FORMAT ([Growth Rate %] * 100, 0 ) & "" & "%",
            "⇩" & ""
                & FORMAT ( ABS ( [Growth Rate %] * 100 ), 0 ) & "" & "%"
        )
    )

Color = IF([Growth Rate %]>0,"Green","Red")

Then build a new page and set it as a tooltip in page size and page info.

Add Color measure in Card visual's Data Lable fx.

Result:

Default

Hover the mouse on Category a 2018

Hover the mouse on Category d 2018

For more infos to Tooltips: Create tooltips based on report pages in Power BI Desktop

You can download the pbix file from this link: How to include growth rate in Clustered Column Chart (Power BI)

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Anonymous]

Hi Rico,

I followed every steps from creating the 3 measures to creating a new page with adding Color measure in Card visual's Data Lable fx.

I think I've missed out some steps which my result does not show like what you have in your screenshot.

Do i have to create a new clustered chart on the same page?

Or it will appear in my existing chart on another page? 

Appreciate your patience in this 🙂

[Anonymous]

Hi @Anonymous 

You don't need to build a new clustered chart on the same page, you just need to add the Fields which is the X axis in your clustered chart in this place.

And the result will appear in your existing chart on another page.

As below is the blog to report page tooltips, it will help you to achieve your goal.

For more infos to Tooltips: Create tooltips based on report pages in Power BI Desktop

You can download the pbix file from this link: How to include growth rate in Clustered Column Chart (Power BI)

If this reply still couldn't help you to solve your problem, please show me more details about your measure or you can share your pbix file with me by your Onedrive for Business.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Anonymous]

Hi Rico,

Thank you so much for your clarification, it works now 🙂

[Anonymous]

Hi @Anonymous 

Please kindly accept the helpful answer as a solution. More people will benefit from it.

Best Regards,

Rico Zhou

[Anonymous]

Hi Rico,

This seems to be a perfect solution!

However I encountered some errors when creating measure (as below screenshot), do you know what is the error pertaining to?

Thank you for your patience in this 🙂

[Anonymous]

Hi @Anonymous 

You need add return and get the result.

More infos to Dax funtcions: Var

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.