Stacked Column Chart Showing % for 2 columns

[Anonymous]

Hi All,

Here is my dataset

Base Hours                                     OverTime Hours                       Craft

8                                                                 0                                       Mill WR

8                                                                 4                                       Elec

8                                                                 3                                       Paint

8                                                                 6                                       Mill WR

8                                                                 0                                       Paint

8                                                                 7                                       Fitter

.....                                                                .....                                     ....

I am trying to create a stacked column chart that shows Base Hours + OverTime hours as a percentage of that chart.                                                                           

It's simply drag and drop if you want the summation. But I'd rather see the percentage distribution while maintaining the relative height of the individual bars (The summation should be by CRAFT, which on the X axis).

So, the craft Mill WR should show 13.98%, 86.02% in blue and green colors instead of 183, 1128. 

Any help is greatly appreciated! Thank you 

[Anonymous · Accepted solution]

Hi @Anonymous ,

I'd like to suggest you to switch to 100% stacked column chart, otherwise you need to write measure formulas to calculate percent and replace original columns fields on stacked column chart.

Base =
DIVIDE (
    CALCULATE ( SUM ( Table[Base Hours] ), VALUES ( Table[Craft] ) ),
    CALCULATE (
        SUM ( Table[Base Hours] ) + SUM ( Table[OverTime] ),
        VALUES ( Table[Craft] )
    )
)

OverTime =
DIVIDE (
    CALCULATE ( SUM ( Table[OverTime] ), VALUES ( Table[Craft] ) ),
    CALCULATE (
        SUM ( Table[Base Hours] ) + SUM ( Table[OverTime] ),
        VALUES ( Table[Craft] )
    )
)

Regards,

Xiaoxin Sheng

[Anonymous]

I'm sorry, but this method gives me a 100% stacked bar chart. I don't want a 100% stacked bar chart. I want a regular bar chart that displays % of each bar (split between the different columns I have in each bar). 

[Anonymous]

Hi @Anonymous , thank you for the suggestion. The DAX expression you have written gives me a 100% stacked column chart. 

I can either use those measures or drag and drop my existing Base and Overtime Columns to create the same graph, right?

^ This is how I am representing it right now. I still don't understand how I can replace the values in the columns in the graph to the left with % values (that are on the right graph). That way, I can get rid of the right graph, while still maintaining the separate height of the columns on the left graph

[Anonymous]

HI @Anonymous ,

>>I can either use those measures or drag and drop my existing Base and Overtime Columns to create the same graph, right?

Yes, both two methods can display percent on chart graph.
For measure formulas, I divide current value with total value to get the percent of total value grouped by current axis label.(you can modify on format tab to choose display mode to percentage)

Regards,

Xiaoxin Sheng