Bar Chart with combined dynamics legend

[Olwin]

Hi there,

I want to make a bar chart like what I've done in Excel with the data structure like below:

	Jan-2021		Feb-2021
	Budget	Sales	Budget	Sales
Branch "A"	1000	900	1200	1500
Branch "B"	1500	1800	1600	1400
Branch "C"	2000	2200	2100	1800

Actually my data will come from 2 tables: Budget and Sales.

Sales Table:

Branch Code	Date	Sales Amount
Branch "A"	1-Jan-2021	900
Branch "B"	1-Jan-2021	1800
Branch "C"	1-Jan-2021	2200
Branch "A"	1-Feb-2021	1500
Branch "B"	1-Feb-2021	1400
Branch "C"	1-Feb-2021	1800

Budget Table:

Branch Code	Date	Budget Amount
Branch "A"	1-Jan-2021	1000
Branch "B"	1-Jan-2021	1500
Branch "C"	1-Jan-2021	2000
Branch "A"	1-Feb-2021	1200
Branch "B"	1-Feb-2021	1600
Branch "C"	1-Feb-2021	2100

Is there any possibilities to create a chart like my Excel Chart in Power BI?

Thanks.

[v-yangliu-msft · Accepted solution]

Hi  @Olwin ,

Here are the steps you can follow：

1. Create calculated column.

Budget Table：
Column = [Date].[Date]&" "&"Budget"

Sales Table：
Column = [Date].[Date]&" "&"Sales"

2. Create calculated table.

Table =
UNION(
SELECTCOLUMNS('Sales Table',"Code",[Branch Code],"Column",[Column],"Amount",[Sales Amount]),
SELECTCOLUMNS('Budget Table',"Code",[Branch Code],"Column",[Column],"Amount",[Budget Amount]))

3. Place [Code] of Table to Axis, [Column] to Legend, and [Amount] to Values.

4. Result:

Best Regards,

Liu Yang

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

[Olwin]

Hi @v-yangliu-msft 

It worked as expected. Thank you for your support.

[Olwin]

Hi @amitchandak, thanks for your guidance. I am now able to create 4 bars for each Branch like my Excel.

I still have question:

1. How can we make a measure name change dynamically? My expection would be:

    MTD Sales = February 2021 Sales

    last MTD Sales = January 2021 Sales

    MTD Budget = February 2021 Budget

    last MTD Budget = January 2021 Budget

so that we can find out the period of the data in that bar chart.

Thank you.

[amitchandak]

@Olwin , You need common brach table and date table and create 4 measures 

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

MTD Budget= CALCULATE(SUM(Budget[BudgetAmount]),DATESMTD('Date'[Date]))
last MTD Budget= CALCULATE(SUM(Budget[BudgetAmount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

Common table

Bridge Table: https://www.youtube.com/watch?v=Bkf35Roman8&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=19

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 :radacad sqlbi My Video Series Appreciate your Kudos.