cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jankura
Helper I
Helper I

How to get a "Total column" in the clustered column chart

Hi,

 

I'm looking to get some inputs to implement a peculiar requirement in a Power BI report.

I need add a "total column" in a clustured column chat.

  • I found out that there is no seamless way to do such a thing in PBI as some of the other things in visuals. This seems to be very easily doable in excel. I wonder why is this not the case in Power BI.
  • My model is something like below (subset of tables):
    • "Project" table: has projects info. Relevant attributes: status code, project-id and other relevant attributes.
    • "Project Type" table: has two fields, project id and dept.
    • "Project Status" table: has two fields status code and status.
  • Relationships are as one can imagine:
    • "Project" <-> "Project Type" on project id (1:M)
    • "Project" <-> "Project Status" on status code (M:1)
  •  Then, I need to have the clustured column chart visual as:-
    • Axis on "Project Type": dept.
    • Legend on "Project Status": status 
    • Values: total no of project
  • Now there is no easy way to do this, it seems. How to get the "Total Column" to show totals of "project type" across legends i.e. "project status". See below visual

Capture.PNG

 

  • To me it seems one way (as I can envision) would be to use some sort of DAX based method. 
  • I created a calculated table, with a column as below, so that the "Total" also shows up in the axis
NewTable = UNION ( VALUES ( 'Project Type '[dept] ), ROW ( "Department", "Total" ) )

 

  • Beyond this, I couldn't quite grasp what to do next:
    • Should I add another column in this new table for relationship with existing model. Wouldn't that cause erros such circular relationship.
    • There should be some measure in the new table as well, which can be used in the clustured column chart visual (in place of the exisring "count of project ids"). How should this measure's DAX look like, so that for "Total", it does totals across all axis/project-types by status/legend but for other axis values it just count projects for that axis.

I referred below but seems it doen't quite fit here.

reference post 

 

Would appreciate any pointers.

Thanks in advance!

1 ACCEPTED SOLUTION

Hi  @jankura ,

 

Sorry for misleading you.Check below method.

Create a measure based on your new table.

Measure = IF(SELECTEDVALUE(NewTable[dept])="Total",CALCULATE(SUM('Project Type'[Value]),ALL(NewTable[dept])),SUM('Project Type'[Value]))

Finally you will see:

Annotation 2020-07-14 173028.png

For details,see attached.

 

 
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

View solution in original post

9 REPLIES 9
v-kelly-msft
Community Support
Community Support

Hi @jankura

 

In clustered column chart, it's unavailable to put a detailed X-axis with an aggregated X-axis together,I have a workaround,that is to create 2 clustered column charts,then put them together,looking like the result you wanna achieve.

See below:

Annotation 2020-07-13 100755.png

You can check the attachment for details.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

@v-kelly-msft Thanks for your response. I had already implemented this (by working with the formatting options of the two charts), but I would imagine this is not a very clean solution. There are are more axis values in future, these formating adjustements would have to be redone otherwise visually these charts may not look good. I'm looking for an alternate solution.

Hi @jankura ,

 

How about replacing the clustered column chart for totals with Multi-row card,the setting for card visual is much easier.See below:

Annotation 2020-07-14 084849.png

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

Thanks @v-kelly-msft. Yes, all of these alternate solutions I have in my backup plan. Maybe I should have included all workarounds I had thought of in the question iteself.

So would you say, to have the "totals column" in the single chart itself is not possible through any method? DAX or sometime else?

Regards.

Hi  @jankura ,

 

Sorry for misleading you.Check below method.

Create a measure based on your new table.

Measure = IF(SELECTEDVALUE(NewTable[dept])="Total",CALCULATE(SUM('Project Type'[Value]),ALL(NewTable[dept])),SUM('Project Type'[Value]))

Finally you will see:

Annotation 2020-07-14 173028.png

For details,see attached.

 

 
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

View solution in original post

@v-kelly-msft Thank you so much !! And no worries for the earlier input, I should have mentioned what all things I have in mind as backup.

Btw for me the total is coming up as the first "column", in your file it is coming up as "last". Did you do something for this? I would like to put it at last.

Appreciate your help. 

 

 

 

Hi  @jankura ,

 

Just ranking the showing order on the  top right button.

 

 
 
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
az38
Super User II
Super User II

@jankura 

maybe you should try to use 2 different visuals placed near each other.  one for detailed, second - for total. Set manually scale of Y-axis for both of them


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

@az38 Thanks for your response. I had already implemented this (by working with the formatting options of the two charts), but I would imagine this is not a very clean solution. There are are more axis values in future, these formating adjustements would have to be redone otherwise visually these charts may not look good. I'm looking for an alternate solution. 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors