Clustered bar chart with a total column

[Fusilier]

Hi,

I currently have this bar chart:

 

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?

[Daniel29195 · Accepted solution]

@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 

[shafiz_p · Accepted solution]

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:

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:

Hope this helps!!

If, Please accept it as a solution!!


Best Regards,
Shahariar Hafiz

[Daniel29195 · Accepted solution]

@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