Bar Chart with Total in Last

[FarhanAhmed]

Problem

Customers ask you to show total values in a bar-chart for all years along with each year individually.

Let’s take an example that you have Sales table which store Sales for each month and a Calendar Table that stores dates and both are being joined with DateKey.

To create a Total we need another table that stores MonthNames and Total in column as Values (named MonthTotal)

You can sort the MonthName with MonthNumber to get the Month Name and Total in the Last.

You join the Calendar and MonthTotal Table with MonthName and keep the relationship Inactive. Data Model will be look like this.

After you have calculated the Model all you need is to create couple of measures.

Amt Total = CALCULATE(SUM(Data[Amt]),ALLSELECTED('Calendar'[Date]))

Total Amount =IF(MAX(MonthTotal[MonthName])="Total",[Amt Total],CALCULATE([Amt Total],USERELATIONSHIP(MonthTotal[MonthName],'Calendar'[MonthName])))

Drop the MonthName from MonthTotal Table and TotalAmount measures in Values and you will get the desired results.

Hopefully, this will help you guys creating more interactive graphs for customers.

Regards

Farhan Ahmed

TotalCalendar.pbix