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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
TeyaSax
Frequent Visitor

Stacked column chart, how to display total

 Need your help!
How to find and display the total on the stacked column chart? The chart with the line(measure) unfortunately, is not readable for customers.
1. Values in the legend are constantly changing, and the solution that was proposed earlier on the forum (create a measure for each value - can not be used)
2. For total - only unique lines(Query$PatientRef) should be counted!
3.Values in the legend must be present.
3.Screenshot_1.jpg

17 REPLIES 17
Clinical_Epi
Advocate II
Advocate II

I think until the column total -- for stacked columns is added to Power Bi there is an option involving the line. I thought this wouldnt work but actually can be made to appear to be a total at the top of each column.

Now this is going to be long winded - but works consistently for me. For this to work the visual is a line and stacked column chart:

The columns are set out thus:

Shared axis: Date  (Presentation D/T)

Column Series: Presenting problem  (this is hosptial based - so for sales would be like Nike, Rebok, etc..)

Column Values: Presentations (so for sales would be a count of product)

Line Values: Presentations (again for sales would be a coundt of product)

 

Now to Viualize this some finagling is required, took me a bit to work it out - but works:

Click on the format for your vizsualization:

1) I did change the X axis on mine due to the amount of dates being used, so min category width Maximum sixe and Inner padding were altered until all totals showed at top (this really would be your last alteration)

2) Data labels = this is where the magic comes in 🙂 

    a) Display units and and postion are auto - I switched text sixe to 8 

    b) customise series is ON   -   it is here that we are going to adjust everything to make our line disappear

    c) so for each Column Value select it and switch "show" to OFF. Do this until you come to the very last one which should be your line value - leave show as ON, display units as NONE, value decimal 0 (in my case), Positions ABOVE, text sixe 8, Font Familly DIN (smallest font). Background OFF

   d) Now click on "Shapes" - shade aread OFF, stroke width MUST BE ZERO, line style left as solid (though has now disappeared)... Marke shape leave as circle and size as 5. Make sure you change Marker colour if you wish it to stand out a bit, I chose light grey as doesnt interfere with my colours.

 

From there I change the sizing of the graph to fit what I want. Below is the end result: (Please note I have a legal requirement not to show you real data. Hence the red blck outs. Suffice to say though that there are columns total and if one whiches they can run thier mouse over the chart to see the indiviual counts or use one of the many filters I run on it. Not bad and a fairly decent result until an better option shows up. Good luck 🙂

 pic.jpg

likelike
Regular Visitor

I used to be able to do this within 30 seconds in Tableau. It's dissapointment for Power BI as a modern BI tool.

Hopefully they'll add this basic feature in the near future. 

 

This is accomplished in Tableau using a reference line. 

https://community.tableau.com/thread/212880

 

 

@likelike same thing can be achieved in PowerBI and it is also few clicks. matter of knowing how to do it.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Can you tell us the steps to show the grand total at the top of bar charts?  I need asap.  Thank you!

@qa4432 there is lot of request on this, can one of you send the sample dataset and I will put together something. Thanks



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

So.... tell us.

@parry2kso would you be so kind and share your knowledge with us, how it can be achieved in PowerBI "with a few clicks"?

Thanks.

@parry2k

Is there a tutorial/documentation provided by Power BI?

I've done a fair amount of search but I haven't been successful.

Ofir
New Member

Any new? I"m stuck too...

you can use stack with line chart and make stroke width of line chart to zero and turn "show marker" on. I'm sure that is closer to what you are looking for, or just turn off the market and it will just show the data label, experiement with it and see what works best for you.

 

lineoff.PNG



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

It Worked Thanks

danextian
Super User
Super User

What I can think of is using a measure as a tooltip that shows the overall total per venuename. The formula is something like

VENUENAME (ALL) =
//calculates the overall total for venuename only, can still be filtered by other columns
CALCULATE (
    [Count of patientref measure],
    ALL ( 'Table'[venuename] )
)

 

Or you can use the new page tooltip (still in preview) to show a bar chart showing count of patientref broken down by revenue name and another without a breakdown. 

 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

thanks for the advice. unfortunately this is not what I need(
chethan
Resolver III
Resolver III

@TeyaSax

 

For total - only unique lines(Query$PatientRef) should be counted!

 

You can use Constant Line from the analytics & you can manually mention the total 

 

Just check this option is this will be workable 

 

 

regards,

Chetan K

I can calculate manually and use lines of a constant on the chart with a line, but  Line sometimes generates confusion on the audience. i need smth like this ^  total sum of the categories can be shown in each column,

on top.Screenshot_2.jpg

 

regards,

Sasha

@TeyaSax,

Please check if Line and Stacked column chart meets your requirement.
1.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks for the advice, but I already wrote that I need a chart without a line!
The amount should be written at the top of the  each column as the block , and the line can not be used (that's the request of the customer)!
sum is the number of unique rows. Is it possible to find the amount (the number of unique lines) and show it on top of the column?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.