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
wynhodgkiss
Helper III
Helper III

include "Total" in 100% stacked bar chart

Hi,

I have seen various answers to similar questions but I am still struggling so any help with this one would be appreciated.

I am trying to include a Total column in a 100% stacked bar chart.

Simply "Product" (x), "Units" (y)  & "Distribution" (legend)

All good, it shows the % split of units by distribution type for each product.

 

To show the same in a table and line chart I've created the 2 following measures:

 

MonthProductTotal = CALCULATE(SUM(Combined[Units]),ALLEXCEPT(Combined,Combined[Month],Combined[Product]))
 
% of Month Product Total = SUM(Combined[Units])/[MonthProductTotal]
 
and a slicer to choose distribution type. There are also slicers for Product & Period. Perfect, the new measures give the same result as the calculated % for the 100% bars and the total row does what it's meant to do.
Quite simply, I just want the nice total row from my matrix table to be also in my 100% stacked chart. 
 
Any ideas on a quick solution would be much appreciated, I feel like I'm missing something really simple
 
thanks
 
 

 

1 ACCEPTED SOLUTION

Hi @wynhodgkiss ,

I believe I can understand your meaning correctly now.

I have created a simple sample , please refer to my pbix file to see if it helps you.

Create another table with total value.

Table 2 = UNION(VALUES('Table'[date]),ROW("Total","total"))

vpollymsft_0-1678672550190.png

Then created a measure.

Measure =
VAR _vale =
    SELECTEDVALUE ( 'Table 2'[date] )
VAR _1 =
    IF (
        _vale = "Total",
        SUMX (
            FILTER (
                ALL ( 'Table' ),
                'Table'[subjdect] = SELECTEDVALUE ( 'Table'[subjdect] )
            ),
            'Table'[VALUE]
        )
    )
VAR _2 =
    CALCULATE (
        SUM ( 'Table'[VALUE] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[subjdect] = SELECTEDVALUE ( 'Table'[subjdect] )
                && 'Table'[date] = SELECTEDVALUE ( 'Table 2'[date] )
        )
    )
RETURN
    IF ( MAX ( 'Table 2'[date] ) = "total", _1, _2 )

vpollymsft_1-1678672622547.png

How to Get Your Question Answered Quickly 

 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

 

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
wynhodgkiss
Helper III
Helper III

That's not what I'm trying to achieve, that post is about a total label per bar, I am looking for a bar that represents the total of all data in the chart

Hi @wynhodgkiss ,

I believe I can understand your meaning correctly now.

I have created a simple sample , please refer to my pbix file to see if it helps you.

Create another table with total value.

Table 2 = UNION(VALUES('Table'[date]),ROW("Total","total"))

vpollymsft_0-1678672550190.png

Then created a measure.

Measure =
VAR _vale =
    SELECTEDVALUE ( 'Table 2'[date] )
VAR _1 =
    IF (
        _vale = "Total",
        SUMX (
            FILTER (
                ALL ( 'Table' ),
                'Table'[subjdect] = SELECTEDVALUE ( 'Table'[subjdect] )
            ),
            'Table'[VALUE]
        )
    )
VAR _2 =
    CALCULATE (
        SUM ( 'Table'[VALUE] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[subjdect] = SELECTEDVALUE ( 'Table'[subjdect] )
                && 'Table'[date] = SELECTEDVALUE ( 'Table 2'[date] )
        )
    )
RETURN
    IF ( MAX ( 'Table 2'[date] ) = "total", _1, _2 )

vpollymsft_1-1678672622547.png

How to Get Your Question Answered Quickly 

 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

 

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-rongtiep-msft
Community Support
Community Support

Hi @wynhodgkiss ,

You can vote here: Display total value of a bar on a stacked bar chart .

 

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-rongtiep-msft
Community Support
Community Support

Hi @wynhodgkiss ,

What form do you want the total columns to appear in the chart? A separate column based on each legend?

If so, it is by design. power BI is now unable to achieve what you need . You can submit an idea for it at   https://ideas.powerbi.com/ideas/   and wait for users with the same needs as you to vote for you to help make it happen as soon as possible.

 

You can try to use Line and Stacked column chart.

Then create a measure with th total value. Put the measure to the Line y-axis. Finally change the stroke width to 0.

vpollymsft_0-1678329282953.png

 

vpollymsft_1-1678329322772.png

How to Get Your Question Answered Quickly 

 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

 

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Screenshot 2023-03-09 094652.pngThanks for your reply , what I'd like to achieve is something like this but not in 2 separate charts. The chart on the left is a column per product and the chart on the right is all products (the total) The colour differentiation is not important.

Are you saying there is not currently any standard option on any Power BI chart to simply add totals and new measures must be created in every case?

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors