March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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.
NewTable = UNION ( VALUES ( 'Project Type '[dept] ), ROW ( "Department", "Total" ) )
I referred below but seems it doen't quite fit here.
Would appreciate any pointers.
Thanks in advance!
Solved! Go to Solution.
Hi @Anonymous ,
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:
For details,see attached.
Hi @Anonymous ,
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:
You can check the attachment for details.
@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 @Anonymous ,
How about replacing the clustered column chart for totals with Multi-row card,the setting for card visual is much easier.See below:
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 @Anonymous ,
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:
For details,see attached.
There is a much simpeler solution than the ones mentioned:
Create a new clustered bar chart under the categorized one.
Place in axis the same measure(s) as in the chart above.
Keep the y-axis empty.
Place in Title of the y-axis 'Total'
The biggest advantage is that you can give this total-chart an other scale and an other format (bold labels for example).
I know this has been a while since you wrote your answer. But could you provide a step-by-step guide as to what you did for us newer folk? Thanks!
Hi All,
I have a similar problem that I am trying to solve. I have been able to achieve till getting the Total, but then if Total is selected then how to highlight Dept D1 and D2 from other visual as D1 and D2 make up Total.
Currently highlighting DI from Total visual displays D1 in other nad so for D2, but my requirment is highlighting Total should highlight both in other visual.
Please advise on how to achieve this?
Hello @v-kelly-msft , I need the totals but for the rows. I try the same approach for it and seem to be stuck. I just used a rand function to introduce dates and pulled it to small multiples. I try creating a table but I'm unable to see the totals when I use clustered chart viz. Any help is greatly appreciated!!!
hi @v-kelly-msft ,
How to edit this if want to show Total irrespective of any selection. If i select any department then i need that department and next to that i need the overall total.
i have a set of information , where i want the grand total as axis, and when i try above measure, i get the total in each bar. for exapmle if summation of three bar is 15, i am getting 15 on top of each bar. not sure how to resolve this, may i have a video to understand this in aproper way?
@create total in column chart
@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 @Anonymous ,
Just ranking the showing order on the top right button.
@Anonymous
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
@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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
86 | |
84 | |
77 | |
49 |
User | Count |
---|---|
160 | |
144 | |
103 | |
74 | |
57 |