cancel
Showing results 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

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
Community Support

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"))

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 )

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.

5 REPLIES 5
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

Community Support

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"))

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 )

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.

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.

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.

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.

Helper III

Thanks 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?