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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Fusilier
Helper III
Helper III

Clustered bar chart with a total column

Hi,

I currently have this bar chart:

bar chart.png 

The orange bar is total company, the green bars are for each department.

It is currently an illusion though, as it is simply two different charts positioned side by side.

Is it possible to create a single chart that displays a total bar as well as individual bars?

2 ACCEPTED SOLUTIONS
Daniel29195
Super User
Super User

@Fusilier 

yes you can

add to your dimension table  a total row. (this way the total will be displayed ). 

 

then in you measure write an swtich statement  : 

m= 

var  total = calculate ( [sales amount]) , removefilters( dim[name]))

var current = [sales amount]

return switch(

true () , 

selectedvalue(dim[name] = "Total"  total , 

current

)

 

hope this helps. 

 

Note :
create variables 

View solution in original post

shafiz_p
Solution Sage
Solution Sage

Hi @Fusilier  This is possible to acheive. To show total in clustered bar/column chart follow the steps.

1. First create a table with distinct category, for your case department and add total row with it. You can use union function. For example, I have used region :

All_Region = UNION(DISTINCT('Sales Territory'[Sales Territory Region]), {"All Region"})

 

2. Create a relationship one to many with the original dimension table. See image below:

shafiz_p_0-1726388380851.png

 

3. Create a measure to evaluate Totals for all category and individual category. See example :

Total_Sales_All_Region = 
VAR _total = CALCULATE([Total Sales], ALL(All_Region[Sales Territory Region]))

VAR _result = 
SWITCH(
    TRUE(),
    SELECTEDVALUE(All_Region[Sales Territory Region]) = "All Region", _total, [Total Sales]
)

RETURN
_result

 

4. Now you can create column or bar chart using the newly created column and measure. See Image below:

shafiz_p_1-1726388646084.png

 

Hope this helps!!

If, Please accept it as a solution!!


Best Regards,
Shahariar Hafiz

View solution in original post

2 REPLIES 2
shafiz_p
Solution Sage
Solution Sage

Hi @Fusilier  This is possible to acheive. To show total in clustered bar/column chart follow the steps.

1. First create a table with distinct category, for your case department and add total row with it. You can use union function. For example, I have used region :

All_Region = UNION(DISTINCT('Sales Territory'[Sales Territory Region]), {"All Region"})

 

2. Create a relationship one to many with the original dimension table. See image below:

shafiz_p_0-1726388380851.png

 

3. Create a measure to evaluate Totals for all category and individual category. See example :

Total_Sales_All_Region = 
VAR _total = CALCULATE([Total Sales], ALL(All_Region[Sales Territory Region]))

VAR _result = 
SWITCH(
    TRUE(),
    SELECTEDVALUE(All_Region[Sales Territory Region]) = "All Region", _total, [Total Sales]
)

RETURN
_result

 

4. Now you can create column or bar chart using the newly created column and measure. See Image below:

shafiz_p_1-1726388646084.png

 

Hope this helps!!

If, Please accept it as a solution!!


Best Regards,
Shahariar Hafiz

Daniel29195
Super User
Super User

@Fusilier 

yes you can

add to your dimension table  a total row. (this way the total will be displayed ). 

 

then in you measure write an swtich statement  : 

m= 

var  total = calculate ( [sales amount]) , removefilters( dim[name]))

var current = [sales amount]

return switch(

true () , 

selectedvalue(dim[name] = "Total"  total , 

current

)

 

hope this helps. 

 

Note :
create variables 

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Top Solution Authors