cancel
Showing results for
Did you mean:
New Member

## 100% Stacked Column Chart Percentage

Hello Community,

Under 100% stacked column chart, can I have the chart considering negative value and show the % (yellow cells) as below?

It looks like the chart will convert negative numbers to positive as the base of the total amount.

Thanks all.

1 ACCEPTED SOLUTION
Community Support

Hi @nctvhk ,

Here's my solution.

1.Unpivot the table in Power Query

2.Because there can only be one field in the axis, we have to merge the two columns to create the following calculated column.

``P&U = [Period]&"-"&[Unit]``

3.Create the following measure

``Measure = DIVIDE(SUM('Table'[Value]),CALCULATE(SUM('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[P&U]),[Attribute]="Sales")))``

4.The result circled in the red box is the result you want, you can check it.

More details in the attachment.

Best Regards,

Stephen Tao

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

4 REPLIES 4
Community Support

Hi @nctvhk ,

Here's my solution.

1.Unpivot the table in Power Query

2.Because there can only be one field in the axis, we have to merge the two columns to create the following calculated column.

``P&U = [Period]&"-"&[Unit]``

3.Create the following measure

``Measure = DIVIDE(SUM('Table'[Value]),CALCULATE(SUM('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[P&U]),[Attribute]="Sales")))``

4.The result circled in the red box is the result you want, you can check it.

More details in the attachment.

Best Regards,

Stephen Tao

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

Solution Sage

Hi @nctvhk

You can use stacked column chart and put your category column which consist value like EBITA etc under legend and put your percentage column under  Values

It will show your negative value as it is.

Thanks,

Samarth

New Member

Thanks @Samarth_18 ,

My dataset is like this:

In this case if I use the stack column solution you provided and take the hard coded percentage column as value, when I select filter/slicer (e.g. Period 1, Unit A+B), it seems the result will just average/sum the hard-coded percentage instead of showing the correct %.

Thanks.

Super User

you need to unpivot the table and create a measure

``Measure = sum('Table'[Value])/CALCULATE(sum('Table'[Value]),ALLEXCEPT('Table','Table'[Period]))``

and use stacked column

pls see the attachment below

Proud to be a Super User!

Announcements

#### Launching new user group features

Learn how to create your own user groups today!