Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

FarhanAhmed

Bar Chart with Total in Last

Problem

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

 

FarhanAhmed_0-1601358870005.png

 

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)

FarhanAhmed_1-1601358870008.png

 

 

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.

 

FarhanAhmed_2-1601358870011.png

 

 

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.

 

FarhanAhmed_3-1601358870013.png

 

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

 

Regards

Farhan Ahmed